Recipe 6.3 Find All Records with Names That Sound Alike

6.3.1 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?

6.3.2 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"
figs/acb2_0605.gif

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 LastName field using the acbSoundex function. In qrySoundex, we used the following calculation to create a new field called Soundex:

    Soundex: acbSoundex([LastName])
  4. Enter criteria for the calculated field that compare that field against the Soundex code of a user-entered parameter. Use the acbSoundex function to obtain the Soundex code of the parameter. We used the following criteria in qrySoundex:

    acbSoundex([Enter Last Name])

    This qrySoundex query is shown in Figure 6-6.

Figure 6-6. The qrySoundex query in design view
figs/acb2_0606.gif
  1. Declare the parameter to be of type Text using the Query Parameters dialog.

  2. Save and run the query.

6.3.3 Discussion

You can find the acbSoundex function in basSoundex in 06-03.MDB. This function takes a last name and returns a four-digit Soundex code for the name. If you look at the fourth column in Figure 6-5, you can see that the Soundex code for all rows is the same. In this case for names sounding like "Jahnsin" the code is "J525". Soundex codes always begin with the first letter of the name followed by three digits ranging between 0 and 6 that represent the remaining significant consonants in the name.

The acbSoundex function is shown here:

Public Function acbSoundex( _   ByVal varSurName As Variant) As Variant     ' Purpose:     '     Takes a surname string and returns a 4-digit     '     code representing the Russell Soundex code.     ' In:     '     varSurName: A surname (last name) as a variant     ' Out:     '     Return value: A 4-digit Soundex code as a variant          Const acbcSoundexLength = 4          On Error GoTo HandleErr     Dim intLength As Integer     Dim intCharCount As Integer     Dim intSdxCount As Integer     Dim intSeparator As Integer     Dim intSdxCode As Integer     Dim intPrvCode As Integer     Dim strChar As String * 1     Dim strSdx As String * acbcSoundexLength     Dim strName As String          ' We add vbNullString to take care of a passed Null     strName = varSurName & vbNullString     intLength = Len(strName)     strSdx = String(acbcSoundexLength, "0")          If intLength > 0 Then         intSeparator = 0     'Keeps track of vowel separators         intPrvCode = 0       'The code of the previous char         intCharCount = 0     'Counts number of input chars         intSdxCount = 0      'Counts number of output chars              'Loop until the soundex code is of acbcSoundexLength         'or we have run out of characters in the surname         Do Until (intSdxCount >= acbcSoundexLength Or intCharCount >= intLength)             intCharCount = intCharCount + 1             strChar = Mid$(strName, intCharCount, 1)                  'Calculate the code for the current character             Select Case strChar                 Case "B", "F", "P", "V"                     intSdxCode = 1                 Case "C", "G", "J", "K", "Q", "S", "X", "Z"                     intSdxCode = 2                 Case "D", "T"                     intSdxCode = 3                 Case "L"                     intSdxCode = 4                 Case "M", "N"                     intSdxCode = 5                 Case "R"                     intSdxCode = 6                 Case "A", "E", "I", "O", "U", "Y"                     intSdxCode = -1                 Case Else                     intSdxCode = -2             End Select                  'Special case the first character             If intCharCount = 1 Then                 Mid$(strSdx, 1, 1) = UCase(strChar)                 intSdxCount = intSdxCount + 1                 intPrvCode = intSdxCode                 intSeparator = 0                          'If a significant constant and not a repeat             'without a separator then code this character             ElseIf intSdxCode > 0 And _              (intSdxCode <> intPrvCode Or intSeparator = 1) Then                 Mid$(strSdx, intSdxCount + 1, 1) = intSdxCode                 intSdxCount = intSdxCount + 1                 intPrvCode = intSdxCode                 intSeparator = 0                          'If a vowel, this character is not coded,             'but it will act as a separator             ElseIf intSdxCode = -1 Then                 intSeparator = 1             End If         Loop         acbSoundex = strSdx     Else         acbSoundex = Null     End If      ExitHere:     Err.Clear     Exit Function HandleErr:     Select Case Err.Number     Case Else         MsgBox Err.Number & ": " & Err.Description, _          vbOKOnly + vbCritical, "acbSoundex"     End Select     Resume ExitHere End Function

The acbSoundex function is based on the Russell Soundex standard algorithm. Soundex is the most commonly used sound-alike algorithm in the U.S. It works by discarding the most unreliable parts of a name, while retaining much of the name's discriminating power. It works best when used with the English versions of names of people of European descent. Its discriminating power is reduced when it is used with very short or very long names or names with a high percentage of vowels. Other sound-alike algorithms may work better in these situations.

The Soundex algorithm was created to work with people's last names. It appears to work reasonably well with people's first names also, but not for names of businesses. Soundex does not work well for business names primarily because these names tend to be longer than people's names, and Soundex encodes only the first four significant characters. We've found that extending the number of encoded characters to eight works better for business names, although this is a nonstandard implementation of the algorithm. You can easily extend the number of encoded characters by changing the acbcSoundexLength constant found at the beginning of acbSoundex. If you decide to do this, however, we suggest you rename the function to something like acbSoundex8 to distinguish it from the standard function.

Soundex will not work satisfactorily with data other than names.



Access Cookbook
Access Data Analysis Cookbook (Cookbooks)
ISBN: 0596101228
EAN: 2147483647
Year: 2003
Pages: 232

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net