Access Cookbook

Find All Records with Names That Sound Alike

Problem

You enter people’s names into a table in which misspellings are a common occurrence. You would like a way to search for a person’s record disregarding slight differences in spelling. You’ve tried using the Like operator with the first letter of the person’s last name, but that produces too many names. Is there any way to search for records that sound alike?

Solution

Access has no built-in sound-alike function, but you can create one that employs a standard algorithm called the Russell Soundex algorithm. Using this algorithm, it’s fairly easy to search for a last name phonetically.

Run the qrySoundex query found in 06-03.MDB. Enter a last name in the query parameter dialog, and qrySoundex will return all records from tblStaff that sound like the name you entered. For example, if you enter the name “Jahnsin” at the parameter prompt, qrySoundex will return the records shown in Figure 6-5.

Figure 6-5. The records returned by searching for “Jahnsin”

To perform Soundex searches in your own applications, follow these steps:

1. Import the basSoundex module from 06-03.MDB into your database.

2. Create a query based on a table that contains a field that holds people’s last names. Include the LastName field and any additional fields you wish to see in the output of the query.

3. Create a calculated field that calculates the Soundex code for the ...