[AccessD] Is there an easier way?

Lavsa, Rich Rich_Lavsa at pghcorning.com
Tue Jun 1 10:59:51 CDT 2004


I have done something like this, but on a smaller scale.  I have a database
that gets updated once a year by outside providers. I didn't want to go
through what you described below so I took the time to build an "Update
Database" that I sent out to all the companies that needed to update
information.  I leave it up to the companies to get the data into the
database the way they want it, then they send the "update database" to me
once a year.  This process is so simple that it is totally left up to the
users to decide when to update the database, so far its been a "no brainer"
push the button marked "UPDATE" which goes out to the network location where
the database expects it to be which will run all the validation code to
update and insert data.

I thought it was a good idea at first, and even better when I convinced the
companies to format their data for me so I wouldn't have to worry about it.
Sounds like a better approach for your purposes as well.  

Maybe feasible, maybe not.. 

G'luck
Rich

-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com] 
Sent: Tuesday, June 01, 2004 11:28 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Is there an easier way?


John,

I worked for a direct marketing company at one time, and our lists
(50,000+) came in formatted differently, with names split differently and
with no consistency.  I did pretty much what you're describing. There really
isn't an easier way ... Except get someone else to do it. <VBG>

Charlotte Foust

-----Original Message-----
From: jwcolby [mailto:jwcolby at colbyconsulting.com] 
Sent: Monday, May 31, 2004 8:19 AM
To: AccessD
Subject: [AccessD] Is there an easier way?


Folks,

I am working on a system where I need to import (and update existing) name /
address data from a bunch of files collected from the states. These files
contain name / address / license information for individuals licensed to
perform physical therapy etc. and come in all kinds of formats, with all
kinds of field names, many of which we don't use, all of which which is
making the problem tougher.  In the end, for now, the files will be
pre-processed (by hand in many cases) to get them into Excel files.  The
preprocessing will include putting the state abreviation into the first two
characters of the file name and a "data type" code into the remaining
characters.  This still leaves the problem of different field names, i.e.
one file may have "Last Name", the next "LastName", the next "Lname" etc.

I have built a system that allows the user to select an Excel file using
File Find dialog.  The file is copied to an common location plus an archive
location with a name that includes the date etc.  The program strips the
state code and looks it up in the state table, and strips the data type code
and looks it up in the data type table.  If all this "passes", then I lookup
the file name in tblImportFile.  If it does not exist I save the file name /
path / stateid and datatype id in a new record.  Most importantly, the file
in the common location is dynamically linked to the FE to allow queries to
be created.  By that I mean that I reach into the table def and change the
"database=" of the connect string to the name/path of the file being
processed so that it points to the file just selected by the user.

If this is a new file (first time processed), the user now "matches" field
names using a pair of combos, one of which displays the field names in our
table and the other displays the field names from the linked excel file.  As
long as the state does not change the field names, this process only occurs
once per file.  The results are stored in tblImportSpec.  tblImportFile
holds the file, path, state id and datatype id.  tblImportSpec holds the ID
from tblImportFile for the file being processed, then the matching field
names from our table / their table.

With me so far?

I go through all this nonsense so that I can dynamically build a query that
"aliases" their field name to our field name, plus grab the state ID and
datatype ID (and import date) and build up a SQL statement that when
executed results in their field names matching my field names, for whatever
fields in their table match fields in our table.  I then save this SQL
string into the SQL property of an existing query def.  Thus at any time you
can open that query and look at the data in an excel spreadsheet, with the
field names matching my field names, and a handful of Ids that match up to
state Ids and data type ids etc.

Once ALL of this is done, I filter out duplicates, allow the user to set up
filters such as last name matches etc. then use the resulting data to build
a temporary table of data.  

The whole point of this exercise is to get a table of data matching people
in our database so that I can update their address information with the
information that the state provides, and of course add new people not in our
database.

The major issues here - 

Various file formats
Various fields, some of which are not needed
Various field names for the fields that are needed
The process needs to be done regularly (at least once per year, sometimes
more often) so it needs to be possible for a user to do this.

Is my solution harder than it needs to be?  Has anyone handled a situation
like this and if so, how do you deal with it?

John W. Colby 
www.ColbyConsulting.com


-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list