[AccessD] "fuzzy logic" search

John,

I have more comprehensive versions, but
here is a simple version of Soundex.

Function Soundex (ByVal S As String) As String
    S = UCase$(Trim$(S))
    Dim Code As Integer: Code = 0
    Dim Last As Integer: Last = 0
    Dim R As String: R = ""
    Dim i As Long: For i = 1 To Len(S)
        Select Case Mid$(S, i, 1)
        Case "B", "F", "P", "V"
            Code = 1
        Case "C", "G", "J", "K", "Q", "S", "X", "Z"
            Code = 2
        Case "D", "T"
            Code = 3
        Case "L"
            Code = 4
        Case "M", "N"
            Code = 5
        Case "R"
            Code = 6
        Case Else
            Code = 0
        End Select
        If (i = 1) Then
            R = Mid$(S, 1, 1)
        ElseIf (Code <> 0 And Code <> Last) Then
            R = R & Code
        End If
        Last = Code
    Next i
    Soundex = Mid$(R & "0000", 1, 4)
End Function

Michael R. Mattys
Try MattysMapLib for MapPoint at
www.mattysconsulting.com



----- Original Message -----
From: "John Colby" <jcolby at colbyconsulting.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Tuesday, October 21, 2003 8:58 PM
Subject: RE: [AccessD] "fuzzy logic" search


> lol.
>
> This is MY database, and I am using an autonumber for the PK of course.  I
> think what you are trying to ask is what am I using for the unique index,
> and the answer is the SSN.  And yes, I have been party to the SSN debate
> before.
>
> In the end, you can't have a unique index on the last name, nor the first
> name, nor both.  The claim data (injury) is doctor chicken scratch  so it
is
> totally unreliable even taken in conjunction with last name etc.
>
> The data is normalized, claimant, claim (and a hundred more).  They enter
a
> claimant.  Then they enter the claim.  If they screw up the claimant then
> the claim is attached to a "duplicate" claimant.  This is not the end of
the
> world, I have a combo in the claim form that allows a supervisor to
reassign
> the claim to a different claimant for exactly this reason.
>
> I am of course open to **constructive!! ideas on how to make this
painless.
>
> John W. Colby
> www.colbyconsulting.com
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Susan Harkins
> Sent: Tuesday, October 21, 2003 8:31 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] "fuzzy logic" search
>
>
> The names aren't consistent either? What's he using as a primary key? I
> think you have more problems than incorrect ss#. :(
>
> Susan H.
>
>
> > 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
> >
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com