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" <[EMAIL PROTECTED]> 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
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
