473,702 Members | 2,450 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Apr 18 '06 #1
4 1717
Takeadoe wrote:
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?
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.



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!

Apr 18 '06 #2
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" <mt********@msn .com> wrote in message
news:11******** **************@ v46g2000cwv.goo glegroups.com.. .
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!

Apr 18 '06 #3
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

Apr 18 '06 #4
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

Apr 18 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

63
5906
by: Jerome | last post by:
Hi, I'm a bit confused ... when would I rather write an database application using MS Access and Visual Basic and when (and why) would I rather write it using Visual Studio .Net? Is it as easy in Visual Studio to create reports and labels as it's in Access?` The advantage of VS.net is that not every user needs Access, right? And that would eliminate the Access version problem as well I guess.
13
2942
by: bill | last post by:
I am trying to convince a client that dotNet is preferable to an Access project (ADP/ADE). This client currently has a large, pure Access MDB solution with 30+ users, which needs to be upgraded. I believe a dotNet solution is better, but I'm trying to be as convincing as possible -- and maybe I'm wrong! I would appreciate any input or references which could help me.
1
4340
by: Dave | last post by:
Hello NG, Regarding access-declarations and member using-declarations as used to change the access level of an inherited base member... Two things need to be considered when determining an inherited base member's access level in the derived class: its access level in the base class and the type of inheritance (public, protected, or private). After this determination is made, the following possibilities exist for manually changing the...
13
13341
by: Simon Bailey | last post by:
I am a newcomer to databases and am not sure which DBMS to use. I have a very simplified knowledge of databases overall. I would very much appreciate a (simplifed) message explaining the advantages and disadvantages of both programs. Many Thanks Simon
0
2974
by: Frederick Noronha \(FN\) | last post by:
---------- Forwarded message ---------- Solutions to Everyday User Interface and Programming Problems O'Reilly Releases "Access Cookbook, Second Edition" Sebastopol, CA--Neither reference book nor tutorial, "Access Cookbook, Second Edition" (O'Reilly, US $49.95), by Ken Getz, Paul Litwin, and Andy Baron, delivers hundreds of practical examples, up-to-date suggestions, and handy solutions to real-world problems that Access users and...
20
3335
by: Olav.NET | last post by:
I am a .NET/C++ developer who is supposed to do some work with Access. I do not know much about it except for the DB part. Questions: *1* I am looking for INTENSIVE books to get quickly up to speed. I like books with practical exercises, and also with test questions (like cert books) *2*
64
5237
by: John | last post by:
Hi What future does access have after the release of vs 2005/sql 2005? MS doesn't seem to have done anything major with access lately and presumably hoping that everyone migrates to vs/sql. Any comments? Thanks
1
3342
by: com | last post by:
Extreme Web Reports 2005 - Soft30.com The wizard scans the specified MS Access database and records information such as report names, parameters and subqueries. ... www.soft30.com/download-1-11975.htm - 31k - Cached - Similar pages MDBSecure 1.0.8.0 - Soft30.com Utility which makes it easy to create secure MS Access Databases, ... MS Access 2000/2003 format. 30 day money back guarantee, 30 day trial. ...
17
4410
by: Mell via AccessMonster.com | last post by:
Is there a way to find out where an application was created from? i.e. - work or home i.e. - if application sits on a (work) server/network, the IT people know the application is sitting there, but is there a way they can find out if that application was put there from a CD or email or created at work? Hint: It's not on a client/server database, just native jet database mdb created on Access 2003 (default 2000)...
37
5229
by: jasmith | last post by:
How will Access fair in a year? Two years? .... The new version of Access seems to service non programmers as a wizard interface to quickly create databases via a fancy wizard. Furthermore, why would you even continue to use Access as a backend when you have a much superior option in SQL express? What about as a future front-end development tool? Let's get serious. Microsoft continues to publish numerous articles and videos on how you...
0
8738
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9234
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9086
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8979
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6575
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5907
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4412
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4667
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2036
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.