[AccessD] "fuzzy logic" search

Here's my suggestion.  I used a BE I had for someone that had ~15,000 user
records.  I then built this query:

SELECT tblPerson.*, [Enter SS Number] AS Criteria
FROM tblPerson
WHERE (((tblPerson.P_txtSocialSecurityNumber) Like "???" & Mid([Enter SS
Number],4,2) & "????" Or (tblPerson.P_txtSocialSecurityNumber) Like "?????"
& Right([Enter SS Number],4) Or (tblPerson.P_txtSocialSecurityNumber) Like
Left([Enter SS Number],3) & "??????"));

That narrows the field a bit.  I saved that query as qryCloseMatches, then I
built this function:

Public Function CheckSSNumber(strOriginal As String, strCurrent) As Long
Dim i As Long
Dim intOffCount As Long
intOffCount = 0
If Len(strOriginal) <> Len(strCurrent) Or IsNull(strCurrent) Then
    CheckSSNumber = -1
End If
For i = 1 To Len(strOriginal)
    If Mid(strCurrent, i, 1) <> Mid(strOriginal, i, 1) Then intOffCount =
intOffCount + 1
    If intOffCount > 2 Then
        Exit For
    End If
Next i
CheckSSNumber = intOffCount
End Function

And built this final query:

SELECT qryCloseMatches.pk_PersonID, qryCloseMatches.P_txtPersonID,
qryCloseMatches.P_txtSocialSecurityNumber, qryCloseMatches.P_txtFirstName,
qryCloseMatches.P_txtMiddleName, qryCloseMatches.P_txtLastName,
qryCloseMatches.P_txtStreetAddress1, qryCloseMatches.P_txtStreetAddress2,
CheckSSNumber([Criteria],[P_txtSocialSecurityNumber]) AS Expr1
FROM qryCloseMatches
WHERE (((CheckSSNumber([Criteria],[P_txtSocialSecurityNumber]))>=0 And
(CheckSSNumber([Criteria],[P_txtSocialSecurityNumber]))<=2))
ORDER BY CheckSSNumber([Criteria],[P_txtSocialSecurityNumber]);

Works pretty fast.  They results are sorted by 'closeness'.  0 is a perfect
match, then 1 and 2 are off by 1 or 2 characters.

Drew

-----Original Message-----
From: John Colby
To: AccessD
Sent: 10/21/03 7:05 PM
Subject: [AccessD] "fuzzy logic" search

My client is having difficulties where the data input people are
entering
duplicate claims because wrong names or SSNs are entered.  He's asked
how
difficult it would be to do a search for the names entered (seems
irrelevant
if it is wrong) but then has asked how difficult it would be to do a
"fuzzy
logic" search for SSNs that are different by up to 2 characters.  I
don't
even have a clue how to attack something like that.  Any ideas (other
than
"out the door, no parachute")?

John W. Colby
www.colbyconsulting.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com