If I have a mdb with with a table contacts and another mdb which has the same
table but I don't know which is the more complete of the two.
Question 1
What would be the quickest / simplest way to integrate the data from the two
tables to create a more complete table without losing data, introducing
duplicate records or deleting records.
Question 2
If there are other complications I have overlooked please advise.
Thanks in advance
1. Use File... Get External Data... Link in one of the databases to link to
the other. Create a unique Index on the combination of fields which uniquely
identify an entry, and run an Append query to migrate the data into that
table.
2. Lots and lots and lots! What is a "duplicate"? If you have
Jim Harris, 312 Main St, yadda yadda
in one database and
James Harris, 312 Main St, yadda yadda
in the other, are they duplicates? You'ld say so (though you might be wrong if
it's James Sr. and Jim Jr.), but Access would not. How about 312 Main St. and
312 Main Street? Same problem.
Access will (with the unique index) discard all the EXACT duplicates (with a
warning message that "x records were not added due to validation error" or the
like); but you'll need to use a USB (Using Someone's Brain) interface to find
the almost-duplicates.
The other problem will be if you have an Autonumber primary key. Don't include
that field in the append!!! The records with matching autonumber values will
have nothing to do with one another, and one set of autonumbers will need to
be discarded (and new autonumbers assigned by the append query). If the
contact numbers are used as foreign keys in other tables, *YOU HAVE A BIG
HASSLE* because all those other tables' foreign keys will become invalid. The
mess can be fixed, but you may be in for a whole bunch of snarky update
queries.
