Simple two table query

[cjackson]

I have two tables (table A and table B) containing contact information for a large number of people.

A number of people appear in both tables.

I need to locate all the people in table A who do not appear in table B. (The format of the names is slightly different in the two tables).

I think this is quite simple but I am struggling to do it.

Thanks in advance for any help or advice

Chris

 

[tlbroadbent]

It is no wonder that you are struggling with this problem. It is easy to find records that don't exist in another table when there is a column upon which you establish a relationship and JOIN the tables. It is difficult if not impossible to write SQL code to find mismatches based on similarities. Depending on the differences, you can try some of the following techniques.

When names are in a different order
TableA: Last,First,Middle
TableB: First,Middle,Last

Select * From TableA
Where Name Not In
(Select a.Name
From TableA As a, TableB As b
Where b.Name Like '*' + Left(a.name,6) +'*'
And a.Name Like '*' + Left(b.name,5) +'*')

Varying the 2nd paramter in the Left function will return different results.

When one table has spaces but the other doesn't.
TableA: LastName, FirstName, Middle
TableB: LastName,FirstName,Middle

Select * From TableA
Where Name Not In
(Select a.Name
From TabelA As a, TableB As b
Where Replace(a.Name, ' ', '')=b.Name)

The Replace function removes the spaces.

If the names are spelled differently, the task is even more difficult. You might try a variation of the first query.

I would begin an effort to synch the tables with the same format and spelling of names. Better yet, create a key column such as an ID number on both tables and use that to match the data in the tables. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.