[ms_access] Re: A Job for Access?

Thu, 20 Apr 2006 06:32:44 -0700


John,

I've embedded my responses in your email below.
--- In [email protected], "John Viescas" <[EMAIL PROTECTED]> 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



Reply via email to