Soundex

[Guest]

I found a lot of information on SoundEx but still don't know how to get it to
work since I'm not a programmer. I inherited this job because someone quit.
Anyway, I have two tables, TableA and TableB. They both have the same fields
of FirstName and LastName. I need to run SoundEx on both of them so they can
then be compared to each other. I read to create a new Module object which I
did called SoundEx but I copied it from the website so I don't know if
anything needs to be changed or where I go from there. Here is the code I
copied and thanks for the help.

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

 

[Albert D. Kallal]

If your file sizes are small (say only a few thousand records), then you can
risk using the function directly in the query builder..and join on he
soundex.

however, for reason of performance, you best use the soundex code, and
crate two new fields

sndFirstName
sndLastName

The reason why I suggest the above is for performance. If ms-access has to
crate the soundex code, and then try and join on that expression, high-speed
indexing can not be used...and it will run very slow.

So, here the general approach.

create the two additional text fields in the 1 st database.
run a update queries that fills the sndFirstName field with the soundex
based on Firstname
run a update queries that fills the sndLastName field with the soundex based
on LastName

do the above same thing for the 2nd table.

create some link from the first table to the 2nd table.

now simply use the query builder to join the two tables on sndFirstName and
sndLastName....

For the soundex code you have, you need to create a code module (since you
don't have coding skills, this is likely going to be difficult for you).

Just call the code module module1

You can then cut + past in your sample code into this module.

You need to make the function public....

Once you sure the code compiles and runs correctly, then you build a update
query that fills those 2 new fields you made with the soundex values

It is assumed you create a index on those two new sndFields...

 

[Guest]

Thank you Albert for helping me. I created the two new fields in both
database tables. I created the module by pasting what I found into it and
called it Module1. In theory I know what will happen in the update query and
I hate to sound really, really stupid but how do I get the function to run?
Where am I telling Access to use this piece of code to update the two new
fields?

 

[Albert D. Kallal]

Thank you Albert for helping me. I created the two new fields in both
database tables. I created the module by pasting what I found into it and
called it Module1. In theory I know what will happen in the update query
and
I hate to sound really, really stupid but how do I get the function to
run?
Where am I telling Access to use this piece of code to update the two new
fields?

Ok, the first thing is to make sure you code compiles, and works.....

while in code...you do a debug->compile...

To "update" the snd fields we made, you fire up the query builder, and drop
in he firstname field.

You then change the query to a update query ( in the menu go query->update
query).

Then, in the "update to" field, you go:

soundEx([FirstName])

Because a update query is EXTREMELY DANGEROUS, you MUST make a copy of the
data before you risk running this, in case you make a mistake, and update he
wrong field..and trash your database!!

you also should likely add the condition

firstname is not null

the reason for this is that the soundex example code does not have
provisions for when you send it a null value...

run the update query....

And, then do the same for the last name field. You have to do this for both
files (on he other hand, if you linked the table from the 2nd file...you
don't have to copy the "code", and you can run build a update query on the
linked tables.

As mentoend, since thsoe udpate querys are very dangrous..do make a copy of
the mdb right before you test/run the udpates....

 

[Guest]

Well, I'm really glad I made that copy because it wiped out all the names in
the field.

Here's what I did. I compiled the code. When I did nothing happened so I
guess I can assume it's alright. I did go back and look at the Debug,
Compile and the Compile was now greyed out. I then closed the code window.

Then I opened the query window. I used the field with the last names as my
"Field". In Update To I typed SoundEx([sndLastName])...which is what I
called the new field I was creating that would have the SoundEx number in it.
I ran the query and it was going to update 17,000 records which is correct
so I said yes but it didn't update any of them and deleted the last name from
each record due to violations.

Thank you Albert for helping me. I created the two new fields in both
database tables. I created the module by pasting what I found into it and
called it Module1. In theory I know what will happen in the update query
and
I hate to sound really, really stupid but how do I get the function to
run?
Where am I telling Access to use this piece of code to update the two new
fields?

Ok, the first thing is to make sure you code compiles, and works.....

while in code...you do a debug->compile...

To "update" the snd fields we made, you fire up the query builder, and drop
in he firstname field.

You then change the query to a update query ( in the menu go query->update
query).

Then, in the "update to" field, you go:

soundEx([FirstName])

Because a update query is EXTREMELY DANGEROUS, you MUST make a copy of the
data before you risk running this, in case you make a mistake, and update he
wrong field..and trash your database!!

you also should likely add the condition

firstname is not null

the reason for this is that the soundex example code does not have
provisions for when you send it a null value...

run the update query....

And, then do the same for the last name field. You have to do this for both
files (on he other hand, if you linked the table from the 2nd file...you
don't have to copy the "code", and you can run build a update query on the
linked tables.

As mentoend, since thsoe udpate querys are very dangrous..do make a copy of
the mdb right before you test/run the udpates....

 

[Guest]

Actually the dialog box says "ShowSoundex" can't update all the records.
What is ShowSoundex?

Well, I'm really glad I made that copy because it wiped out all the names in
the field.

Here's what I did. I compiled the code. When I did nothing happened so I
guess I can assume it's alright. I did go back and look at the Debug,
Compile and the Compile was now greyed out. I then closed the code window.

Then I opened the query window. I used the field with the last names as my
"Field". In Update To I typed SoundEx([sndLastName])...which is what I
called the new field I was creating that would have the SoundEx number in it.
I ran the query and it was going to update 17,000 records which is correct
so I said yes but it didn't update any of them and deleted the last name from
each record due to violations.

Thank you Albert for helping me. I created the two new fields in both
database tables. I created the module by pasting what I found into it and
called it Module1. In theory I know what will happen in the update query
and
I hate to sound really, really stupid but how do I get the function to
run?
Where am I telling Access to use this piece of code to update the two new
fields?

Ok, the first thing is to make sure you code compiles, and works.....

while in code...you do a debug->compile...

To "update" the snd fields we made, you fire up the query builder, and drop
in he firstname field.

You then change the query to a update query ( in the menu go query->update
query).

Then, in the "update to" field, you go:

soundEx([FirstName])

Because a update query is EXTREMELY DANGEROUS, you MUST make a copy of the
data before you risk running this, in case you make a mistake, and update he
wrong field..and trash your database!!

you also should likely add the condition

firstname is not null

the reason for this is that the soundex example code does not have
provisions for when you send it a null value...

run the update query....

And, then do the same for the last name field. You have to do this for both
files (on he other hand, if you linked the table from the 2nd file...you
don't have to copy the "code", and you can run build a update query on the
linked tables.

As mentoend, since thsoe udpate querys are very dangrous..do make a copy of
the mdb right before you test/run the udpates....

 

[Guest]

Hi Albert, I got it to work...I'm so excited...thank you for all of your
help. I did have them switched around the wrong way but it kept throwing me
off course because the error said it was a type conversion and they were the
same.

Thanks again, the help is definitely appreciated.

Thank you Albert for helping me. I created the two new fields in both
database tables. I created the module by pasting what I found into it and
called it Module1. In theory I know what will happen in the update query
and
I hate to sound really, really stupid but how do I get the function to
run?
Where am I telling Access to use this piece of code to update the two new
fields?

Ok, the first thing is to make sure you code compiles, and works.....

while in code...you do a debug->compile...

To "update" the snd fields we made, you fire up the query builder, and drop
in he firstname field.

You then change the query to a update query ( in the menu go query->update
query).

Then, in the "update to" field, you go:

soundEx([FirstName])

Because a update query is EXTREMELY DANGEROUS, you MUST make a copy of the
data before you risk running this, in case you make a mistake, and update he
wrong field..and trash your database!!

you also should likely add the condition

firstname is not null

the reason for this is that the soundex example code does not have
provisions for when you send it a null value...

run the update query....

And, then do the same for the last name field. You have to do this for both
files (on he other hand, if you linked the table from the 2nd file...you
don't have to copy the "code", and you can run build a update query on the
linked tables.

As mentoend, since thsoe udpate querys are very dangrous..do make a copy of
the mdb right before you test/run the udpates....

 

[David W. Fenton]

however, for reason of performance, you best use the soundex
code, and crate two new fields

sndFirstName
sndLastName

The reason why I suggest the above is for performance. If
ms-access has to crate the soundex code, and then try and join on
that expression, high-speed indexing can not be used...and it will
run very slow.

Wouldn't Lyle chime in with his point about ADO and creating
on-the-fly indexes? Has anyone tried that?

 

[David W. Fenton]

Then I opened the query window. I used the field with the last
names as my "Field". In Update To I typed
SoundEx([sndLastName])...which is what I called the new field I
was creating that would have the SoundEx number in it.

Er, you want to update the soundex field to the result of passing
the name field to the SoundEx function. So, the Update line for the
LastName field would be:

SoundEx([LastName])

In the SQL that would be:

SET sndLastName = SoundEx([LastName])

 

[Paul Shapiro]

I see others helped you get this code working. I'll add a note that when you
use the soundex values to try and find imperfect matches, it probably won't
work very well. There are several different soundex schemes, and this simple
one is not very good at making phonetic matches. It was designed 80 years
ago just for American last names.

There are other phonetic analysis schemes out there. Gary Mokotoff at
http://www.avotaynu.com/soundex.html discusses the issues and presents an
alternate scheme that works well for Eastern European names.
Paul Shapiro

 

[Michael Gramelspacher]

....It was designed 80 years ago just for American last names.

And I suppose that my name is not an American last name? <grin>
And how many others are wondering the same thing about their name?

 

[Paul Shapiro]

I don't guess there were too many Shapiro's back then either. More
correctly, it was designed for names that were common in America 80 years
ago. And designed for minimal computational effort.
Paul Shapiro

 

[Guest]

Hi, I did go to the site you suggested but I'm not a programmer so I
wouldn't know what to do with what he listed. I did however, find another
SoundEx code that I will list below. I made a copy so I could get rid of the
other one completely. I created a new module and function with the same
names that I used earlier thinking I wouldn't have to make any other changes
but now it doesn't work. I get an "undefined function 'SoundEx' in
expression". Why would it be wrong if it has the same name and the query
hasn't been changed at all?

Private Function SoundEx(ByVal WordString As String, _
Optional SoundExLen As Integer = 4) As String

Dim Counter As Integer
Dim CurrChar As String

If SoundExLen > 10 Then
SoundExLen = 10
ElseIf SoundExLen < 4 Then
SoundExLen = 4
End If
SoundExLen = SoundExLen - 1

WordString = UCase(WordString)

For Counter = 1 To Len(WordString)
If Asc(Mid(WordString, Counter, 1)) < 65 Or _
Asc(Mid(WordString, Counter, 1)) > 90 Then
Mid(WordString, Counter, 1) = " "
End If
Next Counter
WordString = Trim(WordString)

If Len(Trim(WordString)) = 0 Then
SoundEx = ""
Else
SoundEx = WordString

SoundEx = Replace(SoundEx, "A", "0")
SoundEx = Replace(SoundEx, "E", "0")
SoundEx = Replace(SoundEx, "I", "0")
SoundEx = Replace(SoundEx, "O", "0")
SoundEx = Replace(SoundEx, "U", "0")
SoundEx = Replace(SoundEx, "Y", "0")
SoundEx = Replace(SoundEx, "H", "0")
SoundEx = Replace(SoundEx, "W", "0")
SoundEx = Replace(SoundEx, "B", "1")
SoundEx = Replace(SoundEx, "P", "1")
SoundEx = Replace(SoundEx, "F", "1")
SoundEx = Replace(SoundEx, "V", "1")
SoundEx = Replace(SoundEx, "C", "2")
SoundEx = Replace(SoundEx, "S", "2")
SoundEx = Replace(SoundEx, "G", "2")
SoundEx = Replace(SoundEx, "J", "2")
SoundEx = Replace(SoundEx, "K", "2")
SoundEx = Replace(SoundEx, "Q", "2")
SoundEx = Replace(SoundEx, "X", "2")
SoundEx = Replace(SoundEx, "Z", "2")
SoundEx = Replace(SoundEx, "D", "3")
SoundEx = Replace(SoundEx, "T", "3")
SoundEx = Replace(SoundEx, "L", "4")
SoundEx = Replace(SoundEx, "M", "5")
SoundEx = Replace(SoundEx, "N", "5")
SoundEx = Replace(SoundEx, "R", "6")

CurrChar = Left(SoundEx, 1)
For Counter = 2 To Len(SoundEx)
If Mid(SoundEx, Counter, 1) = CurrChar Then
Mid(SoundEx, Counter, 1) = " "
Else
CurrChar = Mid(SoundEx, Counter, 1)
End If
Next Counter
SoundEx = Replace(SoundEx, " ", "")

SoundEx = Mid(SoundEx, 2)
SoundEx = Replace(SoundEx, "0", "")

SoundEx = SoundEx & String(SoundExLen, "0")
SoundEx = Left(WordString, 1) & Left(SoundEx, SoundExLen)
End If
End Function

 

[Guest]

I figured it out. I remembered reading on a different post that someone's
code wouldn't work either. The individual who resolved it said it was
because it was a Private Function and would only work within his form, so I
took out the word "Private" and ran it to see what would happen and it
worked. Thanks everyone.

I love this site!!

 

[David W. Fenton]

I see others helped you get this code working. I'll add a note
that when you use the soundex values to try and find imperfect
matches, it probably won't work very well. There are several
different soundex schemes, and this simple one is not very good at
making phonetic matches. It was designed 80 years ago just for
American last names.

I use both Soundex and Soundex2 (Soundex2 breaks down the
substitutions into 9 possibilities instead of 6, and lengthens the
resulting string from 4 to 6), and have found that to be very good,
especially when combined with other fields, like Zip, City, Area
Code, etc.

 

[David W. Fenton]

Hi, I did go to the site you suggested but I'm not a programmer
so I wouldn't know what to do with what he listed. I did however,
find another SoundEx code that I will list below. I made a copy
so I could get rid of the other one completely. I created a new
module and function with the same names that I used earlier
thinking I wouldn't have to make any other changes but now it
doesn't work. I get an "undefined function 'SoundEx' in
expression". Why would it be wrong if it has the same name and
the query hasn't been changed at all?

It's because your function is declared as PRIVATE instead of PUBLIC.
Thus, it's not visible outside the module in which it's stored.

It's also just the same Soundex function as you were already using,
so it's not going to improve anything at all. If you compare the
Replace() operations to the Select Case of the original code you
were using, you'll see that the results are all the same, but this
new code is VASTLY less efficient.

Indeed, that code was written by someone who is really not very
bright when it comes to code structures. It's much faster to loop
through the input string and process character-by-character with a
Select Case than to do it the way it's done there, calling an
external function multiple times.

 

[Guest]

Hi David, I would like to give the person I am doing this for the best
result I can. He is going to use this on the first and last name and then
look at address information himself. It sounds like what you use would be
better for him because they he wouldn't have to review as much afterwards.
Is there somewhere I can get both SoundEx and SoundEx2.