Import/Update table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone help??? I'm using Access 2003 and needs to update the table twice
a month. I have to download a raw file which I pull into excel to match up
the fields in my table. Then save it as a text file to import it into Access.
What it doesn't do is update my records. I need to have it change any
information in the record ex: name, address etc., and add the record if it's
not there, however, not all fields needs updating. Plus, I would like to take
the records that are not in the update file and dump them into another table
which I would call the dead table. My unique field is the ssn. How can this
be done?
 
Conceptually, one way to approach this is:

* skip the text file step and link from Access to Excel (and depending on
the form of the 'raw file', you might be able to link directly
* create your permanent (well-normalized) table structure in Access
* create "normalizing" queries that select portions of the data in the
input (Excel or raw) and write to your permanent (well-normalized) tables
* use one query to update existing records from your input
* use another query to add records not already existing
* use a third query to identify your "dead" records (but don't delete
them). By making this also an update query, you can add a "dead as of this
date" field and simply write the date when you update.

If you need this all to run without being touched by human hands, create a
procedure or a macro to do all these.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thank Jeff I'll give this a try.
--
Charligov


Jeff Boyce said:
Conceptually, one way to approach this is:

* skip the text file step and link from Access to Excel (and depending on
the form of the 'raw file', you might be able to link directly
* create your permanent (well-normalized) table structure in Access
* create "normalizing" queries that select portions of the data in the
input (Excel or raw) and write to your permanent (well-normalized) tables
* use one query to update existing records from your input
* use another query to add records not already existing
* use a third query to identify your "dead" records (but don't delete
them). By making this also an update query, you can add a "dead as of this
date" field and simply write the date when you update.

If you need this all to run without being touched by human hands, create a
procedure or a macro to do all these.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Hi Jeff,

I need more assistance on this. I understand about the update query but how
do I write the add record and the move (delete) record query? Any suggesting
will be great. Thank you.
 
If your permanent table has a unique combination of things (or an ID) that's
coming from the input/source, you can create an index in the permanent table
on this/these fields. By making it a Unique index, Access will only allow
one record with that/those values.

You create an append query that attempts to add records. If some of them
are already in your permanent table, the duplicate index situation causes
them to be rejected.

I was not suggesting that you create a move/delete query. Instead, add a
field to your permanent table that you'll use to indicate the record is no
longer of interest. This can be as simple as a Yes/No field (?Am I
interested in this record?), or as complicated as a Date/Time field (on what
date did this record become inactive?). You'd then modify the queries you
use for your "normal" records to exclude any with the relevant value in the
"dead" field.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks again Jeff. I will try it.
--
Charligov


Jeff Boyce said:
If your permanent table has a unique combination of things (or an ID) that's
coming from the input/source, you can create an index in the permanent table
on this/these fields. By making it a Unique index, Access will only allow
one record with that/those values.

You create an append query that attempts to add records. If some of them
are already in your permanent table, the duplicate index situation causes
them to be rejected.

I was not suggesting that you create a move/delete query. Instead, add a
field to your permanent table that you'll use to indicate the record is no
longer of interest. This can be as simple as a Yes/No field (?Am I
interested in this record?), or as complicated as a Date/Time field (on what
date did this record become inactive?). You'd then modify the queries you
use for your "normal" records to exclude any with the relevant value in the
"dead" field.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Back
Top