[ms_access] Re: A Job for Access?

Tue, 02 May 2006 18:26:51 -0700

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






SPONSORED LINKS
Microsoft access database Database development software Database management software
Database software Inventory database software Membership database software

YAHOO! GROUPS LINKS



Reply via email to