Retrieving Similar records from a query

[ianbrown76]

Hi,

I have two tables that I would like to match by name within a query. There are two fields I use for the name in each table, Surname and Forename. When I join the tables together by Surname I qet thousands of records back.

How do I get back the records where the forenames are similair e.g. Rachel may be spealt differently in each, where one field contains Chris and the other Christopher, Adele and Della, where Bert Smith matches Albert Smith or P Smith matches Peter Smith etc etc?

The methods I have used I only seem to get identical matches back. Using LIKE within the Criteria row etc.

If you could help me I'd most appreciate it.

 

[benjamenus]

I'd be happy to be proved wrong but ... I really don't think you can do this. You can't use LIKE "A*" for Bella-Adele and you can't use the sounds like funtcion for P-Peter.

I think you need to embark on the arduous task of data cleansing and also put procedures in place to ensure this can't happen again.

P.S. Even if there was a way to do this, it would offer no accuracy e.g. P Smith could link to Peter and Paul Smith. How would you identify which person is correct?