Merging two identical Access tables

[actimel01]

I know this question has been asked many times before but I can't find an answer that fits my data!

I have two Access databases. The tables in each have the exactly the same fields except for one (which was added into the tables in the second database). Thye do have different records.

I want to merge the records from the tables from in the first database into the tables in the second. Other answers have suggested merging the tables in a query in order to view data from both but in my situation I have a lot more data to enter before I even think about viewing output!

Any suggestions would be much appreciated.

Mel

[nico5038]

You'll first have to think of how the merge will have to take place.
Let's assume you have TableA and TableB and we also assume you have a unique identifier as key.
Then on the key level we can have 3 situations:
1) Keyvalue only in TableA
2) Keyvalue only in TableB
3) Keyvalue match
In case 3 we have an additional situation:
a) Other fields are identical
b) Other field(s) are different.

Using a UNION like
select *, "" as ExtraField from tableA
UNION
select * from tableB;

Will give in situation a) only one row and:

select *, "" as ExtraField from tableA
UNION ALL
select * from tableB;

Will give in situation a) two identical rows.

So some stuff to think about and inform me what you need in what situation.

Nic;o)