Merging Tables

[lem17]

I am looking to merge 2 tables from 2 different databases. The tables
have the same columns and headers, only the data in them is
different. What is the best way to proceed? Thank You

 

[John W. Vinson]

I am looking to merge 2 tables from 2 different databases. The tables
have the same columns and headers, only the data in them is
different. What is the best way to proceed? Thank You

Sort of depends on the tables. Is there a possibility of duplicate data? Do
they have an Autonumber field? If so you're very likely to have duplicate
autonumber values, and if these have propagated to foreign keys in related
tables, you will have *different* records with *identical* keys... a real
problem!

It can be done, using File... Get External Data... Link to connect to the
table, and Append queries to migrate the data; but the duplicate and
autonumber problems can be a real headache.

More details please!

John W. Vinson [MVP]

 

[Guest]

You can use an append query to append (add) the data in one table to the
second table. Is this table related to other tables (Tools > Relationships)?
If so, you may have trouble appending records if referential integrity is
enforced, you are appending to a child (related) table, and there are no
matching records in a parent table.

Here is a KB article that you may find helpful:

How to Append Data from One Table to Another
http://support.microsoft.com/kb/306093


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

From one database or the other, link the similar table. Then create a Union
query that will join them. It should be easy if both have the same field
names.

Remember that a UNION query will discard any exact duplicates. UNION ALL
will return the dupes also.

After you get the Union query to work, you could convert it to a make table
query or just the Union query itself might meet your needs.