A Job for Access?

[Takeadoe]

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

Any and all help on this would be truly appreciated. I'm at a
crossroads here and I need a sign!

Thank you in advance!

[salad]

Re: A Job for Access?

Takeadoe wrote:[color=blue]
> 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 data
> base. 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?[/color]

It doesn't sound like you'll be doing much except cleaning up tables
...iow not much
need for data entry forms or reports. Forms are great for entering data
as you can tell the op if something passes fails upon input into a field
or when a record is saved.

I have no idea what Verity's Teleform V9 is. You can use data in Access
that comes from in a file (txt, xls, etc) and either link to the data
(see File/GetExternalData/Import from the menu) or input it one record
at a time (usually manually).

I'd check out File/GetExternalData/Import and if you know some Basic
(see Open/Close/Input/Get/Put/Write, etc) in help for reading data into
a file if you need to do that.

Once you get the data into the Access tables, I wouldn't think you need
to know much about VBA in your case to massage the data. What you need
to know is how to use the query wizard. Press query/new and look at
your options. You should know how to use them. Once you create a
query, open it in design mode and check out options from the menu (View
SQL/Design, Query and the available types) Here is where you'd update
your data, clean it up, make it ready for SAS. The bottom line is that
you should know enough SQL to design a query...and that's best learned
by using the Query Builder (query/new, etc).

Good luck.




[color=blue]
>
> Any and all help on this is truly appreciated!
>
> Regards,
>
> Mike
>
> Any and all help on this would be truly appreciated. I'm at a
> crossroads here and I need a sign!
>
> Thank you in advance!
>[/color]

[david epsom dot com dot au]

Re: A Job for Access?

I'm not clear, if you can do this in SAS, why do it in Access?

But in Access, I would do data cleaning as a series of update
and append queries.

And if I was starting from a VBS script, I would start by running
all of my queries from the VBS script:

db.Execute "Fix_missing_da te"
db.execute "Fix_missing_we apon"
etc

If I already had the data in Access, I would probably use
an Access Macro instead of a VBS script, because it would
allow me to do VBA functions in my queries.

But when I wanted to take it the next level, I would
probably use VBA in Access to run the queries, because
it gives me greater control of exceptions, as well as
allowing me to use VBA functions in my queries.

(david)


"Takeadoe" <mtonkovich@msn .com> wrote in message
news:1145324581 .039678.302280@ v46g2000cwv.goo glegroups.com.. .[color=blue]
> 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 data
> base. 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
>
> Any and all help on this would be truly appreciated. I'm at a
> crossroads here and I need a sign!
>
> Thank you in advance!
>[/color]

[Takeadoe]

Re: A Job for Access?

Hey Salad, thanks for the guidance. That's just the sort of
information that I was looking for. And you're right, I don't intend
to use Access for forms. No need there. Data entry is handled by the
scanner and the OCR software (Verity Teleform). I've messed with
Access some and have seen how queries work, but I've never had the
opportunity to mess with my own data and put Access to the test. I
will focus on Queries for now and read, read, and bother everyone with
dumb questions. Thanks again.

Mike

[Takeadoe]

Re: A Job for Access?

David,

Thanks for the note. I really appreciate your time. Why not SAS?
Simple, I'm one of just a couple of people that use it in my agency,
while nearly everyone has some knowledge of Access and Excel. Plus,
I'd like to expand my horizons some. The raw data will come to me in
the form of a single large Access table. From there its up to me to
clean it. As noted, I have in the past imported into SAS and then I've
even gone as far as exporting back to Access so others could access the
data. A real pain. Learning Access just makes sense. I will probably
begin tinkering around with last year's data here soon and posting a
bunch of really dumb questions!

Thanks for your help.

Mike