Closest String Match

[mmiram]

Hi all:

Here is the problem I am trying to solve. I have two tables....one is our current clients table from one data source and one is our current clients table from a different data source (different application being used in our office). I created a relationship between the tables based on the full name of the client. I have 500 records in the forst table and close to 700 in the second table. However, when I create a join query, only about 145 records match exactly.

I realized the reason is that the names do not match exactly. For e.g. in data source one, the name is "John Smith", and in data source two the name is "John L Smith" and the names don't match. That is the only field that I can create a join on. Is there any way, I can do a closest string match in Access. I tried breaking the names into first and last and then matching records based on the last name but I am getting too many false matches. What would be the best way to solve this issue. Is there a function that takes a string in one field and finds the closest match in a different field.

Any help would be appreciated.

Thanks.

 

[MichaelRed]

Most general puurpose pseudo (partial) matching processes are more generally used for longer strings (like whole documents) and tend toward finding the differences rather than similarities.

Two approaches which might be of more benefit:

Soundex, which is found several to numerous times in these (Tek-Tips) fora. This was developed by the U.S. Gov. specifically to find similar sounding "names". It was generally used only on the Last Name. It does a 'good job' for that purpose, but become less useful the longer the strings are, and so attempting to use it on a "Full Name" field is probably NOT the approach.

Seperating the full name field into ist' constitiuent (sp?) parts and then attempting to discard an non-name portions (titles, honorifics, etc) and running soundex on the first and last names and creating a 'composit' soundex might get close.

I have had a few occassions to attempt this 'trick', always wit a fair ammount of 'manual' labor after the auto processing. For small recordsets (like your several hundered) it can be useful to at least groom the data and run hte matching process. For larger sets, it is better to figure out some other (more reliable) key.





MichaelRed