Combining two tables with no duplicate names

[Don Barton]

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

Thanks,

Don

[Marshall Barton]

Re: Combining two tables with no duplicate names

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

[Don Barton]

Re: Combining two tables with no duplicate names

Hi Marsh,
Thanks for the suggestion.
Bad news, good news:
Bad New:I tried the Union query, but still couldn't get it to work. .
Almost Good News: I next went in and checked both tables I wanted to
merge, and found that one of the tables had a duplicate name in it.
So I removed the duplicate, made a query that merged both tables (a
little different from the Union query, but did the same thing), and
the data looked great, BUT, when I attached query to a report, I was
still getting duplicates but only sporadically.
Good News: I had added a lookup table to the query that was suppose to
have unique locations, but there were some duplicates in the lookup
table, so this caused duplication in the report. I removed the
duplicates, now all is good.
I would have used the Union query, but I was having to do so much
cleaning up of the data, that I used the "long" way.

Thanks again for your help,

Don

>
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- Hide quoted text -
>
- Show quoted text -