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" <[EMAIL PROTECTED]> 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
>
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.
