Merging Access Application Tables

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

Guest

I have an Access 2000 Application that has about 50 relational tables. This
application contains a subset of records that were exported for me from
another location's application.

I have since made numerous changes to the subset data as well as adding
additional records. So has the owner of the original application.

Note: The tables and relationships have not been changed and the sub-set I
am using still exists in the original application. The Primary Key in each
table is an "Autonumber" field that is used to link the tables to each other.

Is there a way to merge my subset of records back into the original
application and:
- Have it replace the records that were an original sub-set with my updates
- Have it add my new records without over writing any new records that have
been added to the original application by its owner?
 
Hi Ted,

Sounds like there are four categories of record in your subset (I'll
call that B and the original A), requiring different actions:

1) Those that were in the original A before the subset B was exported
and haven't been altered anywhere since. No action required.

2) Those that were in A before B was created, and have since been edited
in B but not in A. For these, update A from B.

3) As for 2, but have been edited separately in both A and B, leading to
two different versions of the record. For these, it's necessary to
decide which version should have priority and update A if B takes
priority.

4) Records added to B after it was created. To be imported into A.

If you can write down rules for working out, from the data in A and B,
which category any given record in B belongs in, merging is possible
though it will takes painstaking and tedious work.

It's a great help if the records include some "audit" fields such as
timestamps for date created and date last modified, the user who was
logged in when a record was modified.

This is particularly helpful in a case like yours, where it's likely
that the same "autonumbers" will have been assigned to totally different
new records in A and in B. Ordinarily, one could identify records in
category 4 by joining the corresponding tables in A and B and
identifying records whose primary key is in B but not in A; but the way
things are a particular autonumber value found in both A and B may
identify either (i) a record in A and the corresponding record in B or
(ii) two unrelated records, one added to A and the other to B.

Records in B that cannot be assigned to one of the four categories
cannot be "merged" into A.
 
Back
Top