Soundex

[CTR]

I know SQL has a Soundex function, but is it possible in Access 2000?

Is anyone aware of a good add-on for Access? _____________

Cliff

 

[MichaelRed]

?Good? them's all related to the eye(s) of the beholder, ain't they?

Public Function Sndx2(ParamArray WdList() As Variant) As String

    'Rhyming Dictionary: PeterMecham
    'By Michael Red:  11/16/2001

    '? Sndx2("Red", "Michael")
    'R3000M2400

    '? Sndx2("Pfizer", "&", "Co.")
    'F2600&0000C0000

    '? sndx2("Bake", "Ache")
    'B2000A2000

    Dim LtrArray As String
    Dim CodArray As String

    Dim WdNum As Integer
    Dim LtrNum As Integer
    Dim LtrPos As Integer
    Dim CodePos As Integer
    Dim blnFirstLtr As Boolean

    Dim SndCode As String
    Dim CurCode As String
    Dim LtrPr As String

    LtrArray = "ABCDEFGHIJKLMNOPQRSTUVWXYZ'-,. /&"   'Letters/Symbols
    CodArray = "012301200224550126230102020000000"   'Codes corresponding to Letters/Symbols

    Const SndLen = 5

    WdNum = 0                               'Initalize WdList Pointer
    Do While WdNum <= UBound(WdList)        'List of &quot;Words&quot; in input

        tName = WdList(WdNum)               'Pick a &quot;Word&quot; to process

        LtrPos = 1
        Do While LtrPos <= Len(tName)               'Do whole Word

            If (LtrPos = 1) Then                    'Flag First Char/DipThong
                blnFirstLtr = True                  'So We Use the Alpha
            End If
                
            LtrPr = Mid(tName, LtrPos, 2)           'Get the Letter / Char PAIR
            MyLtr = basDipThong(LtrPr)              'Check for DipThong

            If (MyLtr = Mid(tName, LtrPos, 1)) Then 'Incr Letter pos in Word
                LtrPos = LtrPos + 1                 'Not Dipthong, One Char Processed
             Else
                LtrPos = LtrPos + 2                 'DipThong, Processed 2 chars
            End If

            CodePos = InStr(LtrArray, MyLtr)         'Find alpha pos of letter
            SndCod = Mid$(CodArray, CodePos, 1)      'Get the Letter Sound Code

            If (blnFirstLtr = True) Then            'First &quot;Char&quot; (or Dip)
                Sndx = Sndx & MyLtr                 'Use the char
                blnFirstLtr = False                 'Set Flag OFF
             Else
                'Not First, Process as &quot;Code&quot;
                If (CurrCode <> SndCod And SndCod <> 0) Then
                    Sndx = Sndx & SndCod
                End If
            End If

            CurrCode = SndCod                       'Prevent Double letter / Code
        Loop            'LtrPos

        'Below will &quot;Pad&quot; & &quot;Trim&quot; each Word to the Number of Chars Desired (e.g. 5)
        Sndx = Sndx & String(SndLen, &quot;0&quot;)
        Sndx = Left$(Sndx, SndLen * (WdNum + 1))

        WdNum = WdNum + 1
    Loop            'WdNum

    Sndx2 = Sndx

End Function
Public Function basDipThong(LtrPr As String) As String

    Dim DipThongs As String
    Dim RepChr As String
    Dim PrCHrPos As Integer

    DipThongs = &quot;TS,TZ,GH,KN,PN,PH,PT,PF,PK&quot;        'Dipthong Pairs
    RepChr = &quot;SZHNNFTFK&quot;                            'Dipthong Replacement Letters

    Idx = InStr(DipThongs, UCase(LtrPr))
    If (Idx <> 0) Then
        PrCHrPos = (Idx + 2) / 3
        basDipThong = Mid(RepChr, PrCHrPos, 1)
     Else
        basDipThong = Left(LtrPr, 1)
    End If

End Function
[code]


 MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[CTR]

Hi Michael,
Thanks for the response. Now let's pretend I don't really know what to do with this (OK, maye I'm not pretending). What I'd like it to do for me is convert all of the first names into one soundex key, then all of the last names into a separate soundex key. I will then run a duplicate report on the soundex values.

With about 100,000 records is there a simple option (query?) to do this? I suspect I need to create the new blank fields first.

Thanks for your suggestion and patience. _____________

Cliff

 

[MichaelRed]

hmmm,

from your response, I suspect that this will be at least at the extreme of your capability at this point.

w/o knowing a lot more about your db schema, i cant really 'code' it for you. assuming that the first and last names are already seperate, you should be able to get the soundex of each in a single query, something like:

SndxLast: Sndx2([LastName])

and

SndxFirst: Sndx2([FirstName])

since you appear to be 'grooming' a mailing/membership list, you will also want to include some record information to permit the grooming process to find the close matches.

It certainly depends on many aspects of the process, but i would probably use the soundex query to generate a new table, simply to ease the (mental) process of getting all of the process elements working where I could 'see' them in various stages -w/o needing to process the entire recordset of 100K recs.



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[CTR]

Michael,
If you are willing to provide a bit more guidance I'd truly appreciate it.

This is not for a mailing list. I am with a non-profit hospital that has a blood collection program. We have a very old and soon to be replaced computer system that is run on a cache database. The programs are written in mumps, or open M I believe it may be called. All of that means nothing to me.

What concerns me is that we have donors that have been entered more than once and may not be eligible to donate under one name, but overlooked and eligible under an alternate name.

The two names are separate.

I understand how to set up the query, but I don't know where to put the code for the soundex function you listed above so the query will use it. I agree that a new table will work best.

Thanks greatly, I've been hoping to work this out for a while.
_____________

Cliff

 

[CTR]

Hmm, no way to edit posts here?

Anyway, I saved it as a module named Sndx2. I removed the word "Public".

Then I used it in a query like you listed above and got the error message of "Undefined function in "Sndx2" expression".

Any tips? _____________

Cliff

 

[MichaelRed]

Shouldn't have a procedure name the same as any (other)object name. Sndx2 in Sndx2 confuses little old ladies (Ms. A). Play nice and she will make tea and serve w/ cookies. Be bad and she just gets obdurate.

Try re-naming the MODULE "modSoundex". While it PROBABLY makes no difference, why did yoy remove the "Public" preamble? MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over