Two table simple 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

 

[genomon]

Hi Chris
Build a new query using the "Find Unmatched" wizard.
It will walk you through the process.
-Geno

 

[UncleJack]

The difference in format for the names may prevent you from actually linking the two tables on that field. If you establish an ID number for each contact and make them the same for persons in both tables then the suggestion to use the Find Unmatched Query Wizard is a good one because you can link on the ID number.

Uncle Jack

 

[BNPMike]

If it helps there are lots of people out there doing this professionally, as companies merge or try and introduce CRM. I was involved in this sort of thing some time ago. If you've got 15 million customers from this customer file and 12 million from that customer file, you've got a few weeks'/months' work to 'de-dupe' them. Some software houses will offer such services. It is not simple as computers are not very good at matching things which are similar to each other rather than the same.

The first thing obviously is to deal with the exact matches and remove them, then work down from there - matches on surname but not initials etc. You must be able to get a SOUNDEX type of function that deals with names (ie Stephens = Stevens), but I don't know where.

Good luck. mike.stephens@bnpparibas.com

 

[cjackson]

Thanks for the advice everyone.

Sorry it has taken a while to respond, got a little bit of data to clean…

This is a complete nightmare...

Prior to doing this we had the two databases de-duped by a professional organisation, however they only matched surname, which is not what we wanted.

So far I have managed to get rid of the 'perfect' matches (using the find unmatched query) and have used 'like' to get rid of some more and that's where it gets messy. I just can't think of a way of doing the rest.

Anymore idea's anyone ?

Thanks in advance

Chris