Thank you for all the help. You've given me a couple of ideas to
explore.
Mike
--- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote:
>
> Mike-
>
> I understand that the Datasheet view of the imported records
appears to list
> them in sequence, but if I run a query against the table, I can't
guarantee
> the sequence - especially if I've added criteria - unless I also
specify an
> Order By clause. Importing the records into a table that has an
AutoNumber
> primary key should do the trick. The solution then is to look for
a record
> with a lower number that has a date - perhaps the highest
sequence number.
> Perhaps something like:
>
> sample.Day_Choice_1 =
> (SELECT Day_Choice_1 FROM Sample As S2
> WHERE S2.ID = DMax("ID", "sample",
> "ID < " & sample.ID & " And Day_Choice_1 Is Not Null))
>
> John Viescas, author
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> Running Microsoft Access 2000
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
> For the inside scoop on Access 2007, see:
> http://blogs.msdn.com/access/
>
> -----Original Message-----
> From: [email protected] [mailto:[EMAIL PROTECTED]
On Behalf
> Of takeadoe
> Sent: Thursday, May 04, 2006 8:41 PM
> To: [email protected]
> Subject: [ms_access] Re: A Job for Access?
>
> John,
>
> My response is inline.
>
> --- In [email protected], "John Viescas" <JohnV@> wrote:
> >
> > Mike-
> >
> > Remember I told you that rows in a table in a relational
database
> have no
> > "order" or "sequence" except as defined by a Primary Key or a
sort
> value.
>
> I'm clear on that. While I have no primary key, the records
appear
> in the order that they entered the system. They are "sorted" per
> se, albeit without a key.
>
> > Now if you assign a "sequence number" to the records as they are
> imported,
> > then maybe you can find a "previous" record from which to pluck
> the date.
>
> This is precisely the issue that I was addressing via an email
just
> moments ago. John - the data comes is being exported from
Teleform,
> which I guess could simply be put as a data capture software and
> then exported to Access. I'm trying to find out if a script can
be
> written that will auto assign each record a unique value
> (corresponding the order in which the records were scanned in a
> batch) to each record before exporting the data to Access. If
not,
> I would need to figure out how to have Access assign a primary
key.
> Realize that the data is not imported to Access all at one time,
but
> rather in batches. I assume that would nto be a problem for
Access -
> it would know where it left off?
>
> I simply used Batch Number because that seemed to be the only
thing
> that defined records close together.
>
> Understood.
> >
> > Where do the records come from to begin with? If from an
external
> file, you
> > could perhaps Append them to a table that has an AutoNumber
> Primary Key -
> > thus that number will logically define the "sequence" of the
> records. If
> > you do that, then the Update query needs to look for any record
> that has a
> > valid date with the highest sequence number less than the
sequence
> of the
> > current record.
>
> John, Assuming that there was a primary key, then it wouldn't be
any
> trouble to use the key to select a date from the prior record?
>
> I could see some if then else constructs with a nested if or two -
>
> If date is good, then output, else do...
>
> Finally, thank you again for all of your help.
>
>
> Mike
>
> >
> > John Viescas, author
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > Running Microsoft Access 2000
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> > For the inside scoop on Access 2007, see:
> > http://blogs.msdn.com/access/
> >
> >
> > -----Original Message-----
> > From: [email protected]
[mailto:[EMAIL PROTECTED]
> On Behalf
> > Of takeadoe
> > Sent: Wednesday, May 03, 2006 7:58 PM
> > To: [email protected]
> > Subject: [ms_access] Re: A Job for Access?
> >
> > John,
> >
> > The update didn't work quite like I had hoped. No fault of
> yours.
> > For those records with a missing date value, the update
> successfully
> > assigned a value. However, it wasn't from the adjacent record
> with
> > a valid date value. Just being within the same batch is not
good
> > enough. In fact, batch really isn't needed, provided the
records
> > are sorted in the order in which they were entered into the
> system.
> > Odds are that the record immediately preceeding and following
any
> > given record are from the same batch, same check station, and
> > represent deer harvested on or very near the same date. So, when
> we
> > hit a record with a missing date, how do we assign that record a
> > value equal to the record immediately preceeding it, assuming it
> has
> > a valid date? In SAS, we would use a retain statement. Which
> > essentially held the value of the first nonmissing date value
and
> > assigned it to each record with a missing date. If the record
had
> a
> > valid date value, it would replace the current value being held
by
> > the retain statement. I don't know if that helps, or even makes
> > sense!
> >
> > Mike
> > --- In [email protected], "John Viescas" <JohnV@> wrote:
> > >
> > > Mike-
> > >
> > > It's SQL. Start a new Query, switch to SQL view, and paste in
> my
> > code. In
> > > my example, I used your "sample" table. You'll need to change
> the
> > table
> > > name if yours is different.
> > >
> > > John Viescas, author
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > Running Microsoft Access 2000
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > > For the inside scoop on Access 2007, see:
> > > http://blogs.msdn.com/access/
> > >
> > >
> > > -----Original Message-----
> > > From: [email protected]
> [mailto:[EMAIL PROTECTED]
> > On Behalf
> > > Of takeadoe
> > > Sent: Wednesday, May 03, 2006 3:24 AM
> > > To: [email protected]
> > > Subject: [ms_access] Re: A Job for Access?
> > >
> > > John - I feel really stupid for asking this, but I'm not
certain
> > > where this code is to go. My first thought was a module and
my
> > > second was in the SQL window of the SQL view.
> > >
> > > Mike
> > >
> > > --- In [email protected], "John Viescas" <JohnV@>
wrote:
> > > >
> > > > Mike-
> > > >
> > > > First you need to understand that records in a table in a
> > relational
> > > > database have no inherent sequence unless you define one.
> Just
> > > because you
> > > > "load" a batch into a table doesn't necessarily mean that
the
> > > records will
> > > > appear in the same sequence as in the original source, so
> there
> > is
> > > logically
> > > > no "previous" record. Here is my suggested query again with
> an
> > > explanation
> > > > of what it is doing:
> > > >
> > > > UPDATE sample
> > > > SET sample.harvest_month =
> > > > DLookUp("[harvest_month]","sample","[BatchNo] = "
> > > > & [BatchNo] & " And
> > > > (Day_Choice_1 Is Not Null Or Day_Choice_2 Is Not Null)"),
> > > >
> > > > sample.Day_Choice_1 =
> > > > DLookUp("[Day_Choice_1]","sample","[BatchNo] = "
> > > > & [BatchNo] & " And
> > > > (Day_Choice_1 Is Not Null Or Day_Choice_2 Is Not Null)"),
> > > >
> > > > sample.Day_Choice_2 =
> > > > DLookUp("[Day_Choice_2]","sample","[BatchNo] = "
> > > > & [BatchNo] & " And
> > > > (Day_Choice_1 Is Not Null Or Day_Choice_2 Is Not Null)")
> > > >
> > > > WHERE (((sample.Day_Choice_1) Is Null)
> > > > AND ((sample.Day_Choice_2) Is Null));
> > > >
> > > > UPDATE sample - tells the query engine that you want to
change
> > rows
> > > in the
> > > > sample table.
> > > >
> > > > The SET clause defines the changes to be made. You can make
> > > multiple
> > > > assignments separated by commas. The first assigns to the
> > > harvest_month
> > > > field a basically random value obtained by doing a "lookup"
> (the
> > > DLookup
> > > > function) for a row in the same batch number (the "[BatchNo]
> = "
> > &
> > > [BatchNo]
> > > > part) that does not have an empty Day_Choice_1 or
Day_Choice_2
> > date
> > > (Is Not
> > > > Null).
> > > >
> > > > The second assignment finds a Day_Choice_1 value from the
same
> > > batch from
> > > > some other row that has a valid date in it. The third fixes
> > > Day_Choice_2.
> > > >
> > > > The WHERE clause tells the query engine to only update
records
> > that
> > > have
> > > > both and empty Day_Choice_1 and an empty Day_Choice_2.
> > > >
> > > > By way of further explanation, the DLookup function is
merely
> a
> > > simplified
> > > > SELECT query. The function has two required parameters and
> one
> > > optional
> > > > parameter:
> > > >
> > > > field name or _expression_
> > > > table or query name
> > > > optional filter
> > > >
> > > > So,
> > > > DLookUp("[harvest_month]","sample","[BatchNo] = "
> > > > & [BatchNo] & " And
> > > > (Day_Choice_1 Is Not Null Or Day_Choice_2 Is Not Null)")
> > > >
> > > > .. is the same as saying in a subquery:
> > > >
> > > > (SELECT [harvest_month]
> > > > FROM sample As S2
> > > > WHERE S2.[BatchNo] = sample.[BatchNo] And
> > > > (S2.Day_Choice_1 Is Not Null Or S2.Day_Choice_2 Is Not
Null))
> > > >
> > > > I didn't use a subquery because a subquery must return one
and
> > only
> > > one
> > > > value, but there might be multiple rows that meet the WHERE
> > > criteria.
> > > > DLookup solves the problem because it always returns just
one
> > > value - the
> > > > first one it finds.
> > > >
> > > > To solve your blank weapon assignment problem, I would need
to
> > know
> > > what
> > > > dates define what seasons. I could then assign via another
> > UPDATE
> > > query the
> > > > proper weapon number based on the dates. For records that
> fall
> > in
> > > the
> > > > archery season, I can give you an _expression_ that will
return
> > > crossbow 55%
> > > > of the time and vertical bow the other 45%. It'll look
> > something
> > > like:
> > > >
> > > > = IIF(Rnd([BatchNo]) * 100 < 55, <crossbow value here>,
> > <vertical
> > > value
> > > > here>)
> > > >
> > > > John Viescas, author
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > Running Microsoft Access 2000
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Paris, France)
> > > > For the inside scoop on Access 2007, see:
> > > > http://blogs.msdn.com/access/
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: [email protected]
> > [mailto:[EMAIL PROTECTED]
> > > On Behalf
> > > > Of takeadoe
> > > > Sent: Thursday, April 20, 2006 8:33 AM
> > > > To: [email protected]
> > > > Subject: [ms_access] Re: A Job for Access?
> > > >
> > > >
> > > > John,
> > > >
> > > > I've embedded my responses in your email below.
> > > > --- In [email protected], "John Viescas" <JohnV@>
> wrote:
> > > > >
> > > > > Mike-
> > > > >
> > > > > I found three records in your sample that have no value in
> both
> > > > Day_Choice_1
> > > > > and Day_Choice_2. The first one is from batch number 2559,
> but
> > > there
> > > > are no
> > > > > other records in that batch.
> > > >
> > > > The only reason there are no other records in the batch is
> > because I
> > > > pulled only those records where STATE = IN. Normally, the
> odds
> > are
> > > > pretty slim that a record with a missing value is going to
be
> > the
> > > first
> > > > record in a batch or in a batch by itself.
> > > >
> > > > The second one is from batch 2672, and there's one other
> record
> > in
> > > that
> > > > batch that has a value of 19 in Day_Choice_2. Would you
> assume
> > > that
> > > > the blank record in that batch should also be 19? The third
> > record
> > > is in
> > > > batch 2944, and there are two other records in that batch
> that
> > > have the
> > > > value 1 in Day_Choice_1.
> > > >
> > > > Yes. Again, under normal circumstances, average batch size
is
> > about
> > > 200
> > > > records. And many of the records in the batch will have
the
> > same
> > > or
> > > > nearly the same date. Thus, when a record is missing a date
> or
> > has
> > > an
> > > > invalid date, I would like to set its date equal to the
> previous
> > > > record's date, provided of course that it was valid. And to
> > > simplify
> > > > our life a little, I think we can eliminate BATCH because
the
> > > records
> > > > will enter in the database by batch. As long as I don't
sort
> > them,
> > > > adjacent records will always be from the same batch and
rarely
> > will
> > > > there be > 2 or 3 records in a row with bad or missing date
> > values.
> > > >
> > > >
> > > > > So, you can fix two of the three records with a query like:
> > > >
> > > > I really need some help deciphering this code. I'll take a
> > stab
> > > at the
> > > > overall goal of the code if you'll be so kind as to fill in
> the
> > > blanks.
> > > > First, your "sample.variable_name" statements are placing
> values
> > > for the
> > > > respective variables into memory (provided the criteria are
> met,
> > > which
> > > > in this case are nonmissing values DAY_CHOICE1 and
> DAY_CHOICE2)
> > for
> > > the
> > > > purpose of populating those records that meet the criteria
of
> > your
> > > WHERE
> > > > statement, i.e., records with missing values for DAY_CHOICE1
> and
> > > > DAY_CHOICE2. The only thing that I can't get my head
wrapped
> > > around is
> > > > the following code:
> > > >
> > > > [BatchNo] = " & [BatchNo] & "
> > > >
> > > > My guess is that you are specifying that the batch of the
> record
> > > meeting
> > > > the WHERE conditions must be equal to the batch of the
record
> > > meeting
> > > > the criteria in your DLOOKUP function. I'm not clear on the
&
> > > either.
> > > > I know that it joins the variables or _expression_ results on
> > either
> > > side
> > > > of it, but I'm not exactly sure what it is returning in this
> > case.
> > > >
> > > >
> > > > > UPDATE sample
> > > > > SET sample.harvest_month =
> > > > DLookUp("[harvest_month]","sample","[BatchNo] = "
> > > > > & [BatchNo] & " And (Day_Choice_1 Is Not Null Or
> Day_Choice_2
> > Is
> > > Not
> > > > > Null)"),
> > > > > sample.Day_Choice_1 = DLookUp
> > > ("[Day_Choice_1]","sample","[BatchNo] = "
> > > > &
> > > > > [BatchNo] & " And (Day_Choice_1 Is Not Null Or
Day_Choice_2
> Is
> > Not
> > > > Null)"),
> > > > > sample.Day_Choice_2 = DLookUp
> > > ("[Day_Choice_2]","sample","[BatchNo] = "
> > > > &
> > > > > [BatchNo] & " And (Day_Choice_1 Is Not Null Or
Day_Choice_2
> Is
> > Not
> > > > Null)")
> > > > > WHERE (((sample.Day_Choice_1) Is Null) AND
> > ((sample.Day_Choice_2)
> > > Is
> > > > Null));
> > > > >
> > > > > I found 10 records with nothing in the weapon column. I
see
> > other
> > > > weapon
> > > > > values from 1 to 5. What values do you want to "randomly"
> > assign
> > > to
> > > > the
> > > > > blank ones? Is a date test also involved?
> > > >
> > > > In the end, each deer is assigned to one of 3 seasons - gun,
> > > archery,
> > > > muzzleloader. As you would imagine, each season has dates,
> > which
> > > means
> > > > that records are assigned to season using date. In the case
> of
> > the
> > > > shotgun season, we can use weapon to assign it to a season,
> > since a
> > > > shotgun is only valid during the gun season. However, if
> weapon
> > is
> > > > muzzleloader, which is valid in both the gun and
muzzleloader
> > > season,
> > > > date must be used. My question regarding random assignment
to
> > > seasons
> > > > deals specifically with archery or bow kills. Since Ohio
> > hunters
> > > can
> > > > use both crossbow and vertical bows during the archery
season,
> > it
> > > is not
> > > > enough to assign records that fall within the archery season
> > dates,
> > > to
> > > > the archery season. They must also be classified as either
> > > crossbow or
> > > > longbow kill. The way that I've handled archery kills with
no
> > > weapon is
> > > > as follows: Annually, 55% of the known archery kills are
> > crossbow
> > > and
> > > > 45% vertical. Thus, if I have 100 archery kills without a
> > weapon
> > > type,
> > > > I randomly select 55 and 45 and assign them to crossbow and
> > vertical
> > > > bow, respectively. This is where the random assignment of
> > weapon
> > > type
> > > > comes into play.
> > > >
> > > > I think that is it for now. Again, I want to thank you for
> your
> > > time
> > > > thus far, and look forward to hearing from you.
> > > >
> > > > Mike
> > > >
> > > >
> > > >
> > > >
> > > > > John Viescas, author
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > Running Microsoft Access 2000
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas.com/
> > > > > (Paris, France)
> > > > > For the inside scoop on Access 2007, see:
> > > > > http://blogs.msdn.com/access/
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: [email protected]
> > > [mailto:[EMAIL PROTECTED] On
> > > > Behalf
> > > > > Of takeadoe
> > > > > Sent: Wednesday, April 19, 2006 8:26 AM
> > > > > To: [email protected]
> > > > > Subject: [ms_access] Re: A Job for Access?
> > > > >
> > > > > John,
> > > > >
> > > > > I've uploaded a small sample of data and called it db1.
The
> > file
> > > > > description is "Deer Harvest Data." A couple of comments
> about
> > the
> > > > > file. Either Day_choice 1 or day_choice_2 holds the day of
> the
> > > > > month the deer was harvested. Its a long story as to why
it
> is
> > set
> > > > > up that way, but in short it has to do with the design of
> the
> > form
> > > > > that is used to collect the data. In another version of
this
> > > table,
> > > > > I actually created a date variable by combining the
> > information
> > > from
> > > > > the harvest month and day choice fields and assigned year
> > based on
> > > > > harvest month. From what I recall, it really didn't work
> that
> > well
> > > > > though. Since our season runs through Jan. year was 2006
when
> > > > > hrvst_month was 1 and 2005 for all others.
> > > > >
> > > > > That's it for now. If you have additional questions,
holler.
> > > > >
> > > > > Mike
> > > > >
> > > > > --- In [email protected], "John Viescas" JohnV@
> wrote:
> > > > > >
> > > > > > Mike-
> > > > > >
> > > > > > Sorry, I don't "speak" SAS. You can zip and upload a
sample
> > > > > database to the
> > > > > > files section of this Yahoo list.
> > > > > > http://groups.yahoo.com/group/ms_access/files/
> > > > > >
> > > > > > John Viescas, author
> > > > > > Building Microsoft Access Applications
> > > > > > Microsoft Office Access 2003 Inside Out
> > > > > > Running Microsoft Access 2000
> > > > > > SQL Queries for Mere Mortals
> > > > > > http://www.viescas.com/
> > > > > > For the inside scoop on Access 2007, see:
> > > > > > http://blogs.msdn.com/access/
> > > > > >
> > > > > >
> > > > > > -----Original Message-----
> > > > > > From: [email protected]
> > > [mailto:[EMAIL PROTECTED]
> > > > > On Behalf
> > > > > > Of takeadoe
> > > > > > Sent: Tuesday, April 18, 2006 8:31 AM
> > > > > > To: [email protected]
> > > > > > Subject: [ms_access] Re: A Job for Access?
> > > > > >
> > > > > >
> > > > > > Hi John,
> > > > > >
> > > > > > First, I'd like to thank you for taking the time to
reply.
> I
> > > know
> > > > > > everyone is busy, so thank you! PREVIOUS RECORD - Our
> forms
> > are
> > > > > > delivered in batches from deer check stations all over
the
> > > state.
> > > > > The
> > > > > > records are scanned as batches, which means that there
is
> a
> > very
> > > > > good
> > > > > > chance the if a record is missing a date, its probably a
> > good
> > > bet
> > > > > that
> > > > > > the date on the previous record (from the same check
> > station) in
> > > > > the
> > > > > > batch is either going to be the correct date (for busy
> times
> > of
> > > the
> > > > > > hunting season) or very close to the actual date of
> harvest.
> > The
> > > > > SAS
> > > > > > code below is used for this purpose. As for a peek at the
> > > > > structure of
> > > > > > the table, I would be delighted to send you a small
sample
> > of
> > > the
> > > > > data
> > > > > > if you'd like to give me an email address or other
> > directions on
> > > > > how I
> > > > > > might get it to you. At any rate, thank you for taking
> time
> > to
> > > > > reply.
> > > > > >
> > > > > > Mike
> > > > > >
> > > > > >
> > > > >
> > >
> >
>
*********************************************************************
> > > > > ***\
> > > > > > ***************************
> > > > > >
> > > > > > ADDRESSES DATE ISSUES - CONVERTS DATE VARIABLES TO SAS
> DATE
> > > VALUE
> > > > > AND
> > > > > > ASSIGNS A DATE TO RECORDS
> > > > > >
> > > > > > WITH MISSING VALUES
> > > > > >
> > > > > >
> > > > >
> > >
> >
>
*********************************************************************
> > > > > ***\
> > > > > > ***************************;
> > > > > >
> > > > > >
> > > > > >
> > > > > > DATA TEMP.A;
> > > > > >
> > > > > > LENGTH ORIGINALDATE $ 30;
> > > > > >
> > > > > > SET SASUSER.QURY5798;
> > > > > >
> > > > > >
> > > > > >
> > > > > > IF ('01OCT05'd LE DATE LE '31JAN06'd) THEN DO;
> > > > > >
> > > > > > ORIGINALDATE='GOOD';
> > > > > >
> > > > > > END;
> > > > > >
> > > > > >
> > > > > >
> > > > > > ELSE DO;
> > > > > >
> > > > > > ORIGINALDATE='BAD';
> > > > > >
> > > > > > END;
> > > > > >
> > > > > >
> > > > > >
> > > > > > RUN;
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > DATA TEMP.B;
> > > > > >
> > > > > > RETAIN NEWDATE_RETAINED;
> > > > > >
> > > > > > SET;
> > > > > >
> > > > > > IF ORIGINALDATE='GOOD' THEN DO;
> > > > > >
> > > > > > NEWDATE_RETAINED=DATE;
> > > > > >
> > > > > > END;
> > > > > >
> > > > > > DATA TEMP.C;
> > > > > >
> > > > > > SET;
> > > > > >
> > > > > > DROP DATE;
> > > > > >
> > > > > >
> > > > > >
> > > > > > DATA TEMP.D;
> > > > > >
> > > > > > SET;
> > > > > >
> > > > > > RENAME NEWDATE_RETAINED=DATE;
> > > > > >
> > > > > >
> > > > > >
> > > > > > RUN;
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > --- In [email protected], "John Viescas"
<JohnV@>
> > wrote:
> > > > > > >
> > > > > > > Mike-
> > > > > > >
> > > > > > > Maybe. What constitutes "most recent" record for
> assigning
> > > > > dates? If
> > > > > > > there's some other valid field in the data that gives
> you
> > the
> > > > > most
> > > > > > recent
> > > > > > > record, you can perhaps use a query to assign the
> missing
> > > dates.
> > > > > You
> > > > > > can
> > > > > > > definitely use a query to "randomly" assing crossbow
or
> > > longbow
> > > > > to
> > > > > > weapon
> > > > > > > type. I'd need to know more specifics about the
> structure
> > of
> > > the
> > > > > table
> > > > > > -
> > > > > > > the table name and relevant field names - to give you
a
> > more
> > > > > specific
> > > > > > > answer.
> > > > > > >
> > > > > > > John Viescas, author
> > > > > > > Building Microsoft Access Applications
> > > > > > > Microsoft Office Access 2003 Inside Out
> > > > > > > Running Microsoft Access 2000
> > > > > > > SQL Queries for Mere Mortals
> > > > > > > http://www.viescas.com/
> > > > > > > (Paris, France)
> > > > > > > For the inside scoop on Access 2007, see:
> > > > > > > http://blogs.msdn.com/access/
> > > > > > >
> > > > > > >
> > > > > > > -----Original Message-----
> > > > > > > From: [email protected]
> > > > > [mailto:[EMAIL PROTECTED] On
> > > > > > Behalf
> > > > > > > Of takeadoe
> > > > > > > Sent: Monday, April 17, 2006 9:05 PM
> > > > > > > To: [email protected]
> > > > > > > Subject: [ms_access] A Job for Access?
> > > > > > >
> > > > > > > Hey Gang, I'm gearing up to retool for the upcoming
deer
> > > season
> > > > > here
> > > > > > > in Ohio and I could use some help with very general
> > questions
> > > > > about
> > > > > > > direction. I will be scanning nearly 210,000 forms
that
> > > capture
> > > > > deer
> > > > > > > harvest information. We use Verity's Teleform V9
> > enterprise to
> > > > > > > capture the scanned data.
> > > > > > >
> > > > > > > To date, I've not done any scripting (essentially VBA)
> and
> > > have
> > > > > not
> > > > > > > set up any real fancy rules during verification. If
> there
> > are
> > > > > bad or
> > > > > > > missing values, they end up as bad or missing values
in
> the
> > > > > Access
> > > > > > > database. From there, the data are imported into SAS,
> > which is
> > > > > where
> > > > > > > I clean the data and replace missing and bad values.
> I've
> > also
> > > > > used
> > > > > > > SAS to assign values to each record based on the
scanned
> > > > > information.
> > > > > > > For instance, HARVEST DATE is used to assign each
record
> > to a
> > > > > SEASON
> > > > > > > (gun, crossbow, handgun, etc) and records with missing
> date
> > > > > values
> > > > > > > are assigned a valid date using the most recent record
> > with a
> > > > > valid
> > > > > > > date value (valid in this case is legal season dates).
> > Other
> > > > > things
> > > > > > > that I've used SAS to do is randomly assign records to
> one
> > of
> > > two
> > > > > > > seasons when the weapon type is missing (archers can
use
> > > either a
> > > > > > > crossbow or longbow during the archery season - a
valid
> > date
> > > only
> > > > > > > tells you it was an archery harvest, but you have no
> idea
> > if
> > > it
> > > > > was a
> > > > > > > crossbow or longbow). Long story short, in the end,
all
> or
> > > > > nearly all
> > > > > > > of the records are made "complete" with the help of
SAS.
> My
> > > > > question
> > > > > > > for the group is can Access do things like this or
> should
> > I
> > > try
> > > > > and
> > > > > > > have the data as clean as possible before it gets to
> > Access?
> > > > > > > Unfortunately, to clean if before it gets to Access
> means
> > > that I
> > > > > have
> > > > > > > to learn to write custom scripts and I'm not sure how
> much
> > I
> > > can
> > > > > do
> > > > > > > with scripting. In case it isn't immediately obvious,
I
> > KNOW
> > > > > LITTLE
> > > > > > > MORE THAN HOW TO SPELL ACCESS, but I'm anxious to
learn!
> > > > > > >
> > > > > > >
> > > > > > > So you see, I really am at a critical point - do I
learn
> > what
> > > > > amounts
> > > > > > > to VBA or focus more on Access and figure out how to
> make
> > it
> > > do
> > > > > what
> > > > > > > I want?
> > > > > > >
> > > > > > >
> > > > > > > Any and all help on this is truly appreciated!
> > > > > > >
> > > > > > >
> > > > > > > Regards,
> > > > > > >
> > > > > > >
> > > > > > > Mike
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Yahoo! Groups Links
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > [Non-text portions of this message have been removed]
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > Yahoo! Groups Links
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Yahoo! Groups Links
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
>
>
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
SPONSORED LINKS
| Microsoft access database | Database development software | Database management software |
| Database software | Inventory database software | Membership database software |
YAHOO! GROUPS LINKS
- Visit your group "ms_access" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
