Merge Two Tables

[aomarc]

I have two tables in access each with the same type of unique identifier. Some records overlap, and some are new additions. Each table has different data. I want to merge these tables. Any help would be appreciated.

IE:

Table 1
ID A  B  C
2  1  1  1
3  1  1  1
4  1  1  1
Table 2
ID D  E  F
2  1  1  1
4  1  1  1
5  1  1  1
8  1  1  1
Result
ID A  B  C  D  E  F
2  1  1  1  1  1  1
3  1  1  1
4  1  1  1  1  1  1
5           1  1  1
8           1  1  1

The ones represent data and the blanks NULL fields

Thanks

[nico5038]

You can use a combination of a UNION and GroupBy query for this.
First create a union like:
[code=sql]
select KeyNum, Text1, Num1, "" as Text2, 0 as Num2 from tblA
UNION
select KeyNum, "", 0, Text1, Num1 from tblB
[/code]
Next create a groupby query based on the KeyNum field as "GroupBy" and MAX() on the other fields.

Getting the idea?

Nic;o)

[aomarc]

Thanks Nic! Worked perfectly!