Access 2007 SQL Merge tables without creating duplicates

1
votes

I would like to add the unique values of tblA to tblB without creating duplicate values based on multiple fields. In the following example, FirstName and LastName determine a duplicate, Foo and Source are irrelevant.

tblA:

FirstName  LastName  Foo  Source
John       Doe       1    A
Jane       Doe       2    A
Steve      Smith     3    A
Bill       Johnson   2    A

tblB: 

FirstName  LastName  Foo  Source
John       Doe       1    B
Bob        Smith     5    B
Steve      Smith     4    B

This is the result I want:

tblA: 

FirstName  LastName  Foo  Source
John       Doe       1    A
Jane       Doe       2    A
Steve      Smith     3    A
Bill       Johnson   2    A
Bob        Smith     5    B

Here's an equivalent of the code I've tried:

INSERT INTO tblA
SELECT B.* FROM tblB AS B
LEFT JOIN tblA AS A ON A.FirstName = B.FirstName AND A.LastName = B.LastName
WHERE A.FirstName IS NULL

And this is the result I get:

tblA:

FirstName  LastName  Foo  Source
John       Doe       1    A
Jane       Doe       2    A
Steve      Smith     3    A
Bill       Johnson   2    A
John       Doe       1    B
Bob        Smith     5    B

Steve Smith from tblB is ignored, which is good. John Doe from tblB is added, which is bad. I've spent way too much time on this and I've inspected the data every way I can think of to ensure John Doe in tblA and tblB are the same first and last name. Any ideas on what could be going wrong?

Update: FYI, on my real tblB, about 10,000 of 30,000 should be moved to tblA. This is actually moving over 21,000. The problem is this is one step of a common process.

3
sqlms-access-2007
Regarding your recent edit, it is still valid to test one case as per my comment. If it proves to be hidden characters, then various other approaches can be considered, depending on the character. For example, Excel sometimes transfers with character 255, which looks like a space. - Fionnuala

3 Answers

1
votes

When I try:

SELECT tbb.*
FROM tbb 
LEFT JOIN tba 
ON (tbb.FirstName = tba.FirstName) 
   AND (tbb.LastName = tba.LastName)
WHERE (((tba.LastName) Is Null));

The only line returned is:

Bob        Smith     5    B

Is it possible that John Doe has a hidden character?

0
votes

Edit : Sorry, it doesn't work on Access2007

You have many way to do that :

INSERT INTO tblA 
SELECT B.* FROM tblB AS B 
WHERE B.firstname, B.lastname NOT IN (select firstname, lastname from tblA)

Or

INSERT INTO tblA 
SELECT * FROM tblB
MINUS
SELECT * FROM tblA
0
votes

This one works in Access.

You can run it to infinity - it won't add more rows than needed:

INSERT INTO tblA
SELECT B.*
FROM tblB AS B
WHERE (((B.FirstName) Not In (select firstname from tblA))
AND ((B.LastName) Not In (select firstname from tblA)))