Comparison Query on Two Tables

[databasedonr]

I have two tables -- both have Surname, First name, Middle Name fields.

I want to build a query where I can compare the records in tableA with the records in tableB where the Surname in A is "like" the Surname in B.

The "like" is important, because I am dealing with potentially different spellings. Let's say I have MacDonald in tableA and a McDonald in tableB that may be the same -- the only diffence being the typing ability of the data entry staff. I need to know this!

Also, if I can master the Surname, I would like to be able to do similarly if tableA.Surname Like tableB.Surname and tableA.FirstName like tableB.FirstName.

Is this even remotely possible? I can do the query where the records match (where tableA.Surname = tableB.Surname).

I've tried simply using LIKE as the operator rather than = in the query, but I get exactly the same results.

 

[crosmill]

There's no easy way round this I'm affraid, Access doesn't really support 'Fuzzy Logic' like that, you'll have to build it in yourself.

The way I get round the problem, if I'll be using it often is to build dozens of quries and use macro's to run them.

What I do is to create a new column and append 'Cleansed' data to compare it against similary treated column in the other table. So, I'll do things like checking the string for puncuation and removing it and comparinging it, I'll remove all the spaces etc. then I'll create a matchkey, which is different parts of all columns that could contain unique data, eg. LEFT(Surname,5) & LEFT(Postcode, 4) & LEFT(Address1, 5).

I 'rate' each process as to how confident I am with the match, I run the most reliable first and append ID's and don't include them in the next queries.

hope this helped