Don Barton wrote:
>I have 2 tables, Table 1 has Name, NameID, and A, B, C fields.
>Table 2 has Name, NameID, and D, E fields.
>Several of the Names/NameID are the same in both databases. I want my
>merged the tables so each record looks like; Name, NameID, A, B, C, D,
>E. I have merged them successfully but I am getting two records for
>each name, one record for A, B, C fields, and another record for the
>D, E fields.
>
>Example,
>Name NameID A B C D E
>Smith, John 123 X X
>Smith, John 123 X X
>
>What I want to see is:
>
>Name NameID A B C D E
>Smith, John 123 X X X X
Looks like you used a simple UNION query to do the merge.
I think you might want a full outer join query something
like:
SELECT t1.Name, t1.NameID, t1.A, t1.B, t1.C, t2.D, t2.E
FROM [Table 1] As t1 LEFT JOIN [Table 2] As t2
ON t1.NameID = t2.NameID
UNION ALL
SELECT t2.Name, t2.NameID, Null, Null, Null, t2.D, t2.E
FROM [Table 2] As t2 LEFT JOIN [Table 1] As t1
ON t2.NameID = t1.NameID
WHERE t1.NameID Is Null
--
Marsh
|
