[ms_access] Re: A Job for Access?

Wed, 03 May 2006 08:47:06 -0700

Thank you John - That's what I thought!
--- 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



Reply via email to