RE: [ms_access] Re: A Job for Access?

Wed, 19 Apr 2006 09:57:30 -0700

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 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.

So, you can fix two of the three records with a query like:

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?

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