Comparing similar names

Topic

New Reply

I would like for my database to run a check against existing last names in my participant table each time an end user enters a new name. For example, automatically running a query based on wildcard such as An* and bring up a form showing all participants’ last name that starts with An?

Reason for this is because I’ve been finding too many records that are based on same person, but has slight variation on their last name! For example, Brent Andersen, Brent Anderson, Brent Anderssen etc. (They are all the same person.) My thinking was that if I could somehow create a dynamic query that runs wildcard query and brings up list of names, maybe that would encourage end-users to verify that there isn’t an existing record already.

Reply | Quote

Replies

For that, your best bet is a soundex comparison. Soundex is an algorithm that creates a numeric value for a word based on values assigned to each letter group. For example, all vowels are assigned a zero value; b, p , t, and d are all assigned the same number; m and n are assigned the same number, and so forth. Many of versions uses the actual first letter of the word and create a 4-digit value for the rest of the word, or at least as much of the word as they can fit into 4 digits. If the word is short, you get zero placeholders in the last digits.

My biggest gripe about soundex is that for large tables, you need to create the soundex string when the record is created, otherwise, the update query runs forever.

Reply | Quote