Matching "Similar" records (1 Viewer)

[ianward]

Hi - I am working on a database where i need to update a table with a "UniquePersonID"

I have a master table which stores:
UniquePersonID
Surname
Forename
DOB
SEX
Postcode

This table is approx 600k records, now the problem i have is that i have further tables which contain Surname, Forename, DOB, Sex and Postcode and need to resolve these with the "UniquePersonID", unfortunatley in some cases names, DOB's may have been spelt slightly differently, (i.e. "Abbas" should have been "Abbass" or the DOB has been entered with a typo)

I have managed to do an exact match and update the majority of records but i am still left with just over 16,000 records to match manually... I have created a form which allows me to tick which boxes to search against to resolve record by record, but estimates suggest that this will take around 12 solid days work

Is there a way of matching similar records and coming up with a probability score that anyone out there is aware of???

Many thanks

Ian

 

[neileg]

Access doesn't do fuzzy matching. You might whittle down your 16k by matching the first X characters in the names using Left() in a calculated field.

 

[ianward]

Thanks for the reply Neil, It could be worth trying that - running the same query with Left, Mid & Right (separatley of course) - as doing the manual way i am averaging around 200 matches per hour... I think i will have lost the will to live when I get to the end if I have to do them all manually!!!