Merging Access Application Tables

[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?

 

[John Nurick]

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.