# Thread: Finding Dupe Qry w/ a Twist! (97)

1. ## Finding Dupe Qry w/ a Twist! (97)

Hi,
I am in the middle of creating a database w/ data coming from various soures
(all of these sources have most of the same field names).
What I need to do is create a find duplicate query to map out
the dupes to make sure that only one unique record for each customers goes into the system.
The problem I have is that I may have the same customer name
in two systems, and the customers may be spelled diferently

Example:

Data Source One-A B C, Co.Inc.
Data Source Tow -ABC Company, Inc.

I don't think a reg. find dupe qry would find this using customer name.

but these may difer as well.

To make a long story short, does anyone know of a way to design a
Find Duplicate query to look for the first 10 o or so charecters, and bring up
those that match?

Thanks

2. ## Re: Finding Dupe Qry w/ a Twist! (97)

One idea would be to use Soundex values. Soundex is an algorithm that calculates a short code from a string. The code is the same for similar sounding strings, ignores spaces, capitalization etc.

Here is the code for an implementation of standard Soundex:

Public Function Soundex(Word As String) As String
' Original Soundex function
Dim strCode As String
Dim strChar As String
Dim lngWordLength As Long
Dim strLastCode As String
Dim i As Integer
' Grab the first letter
strCode = UCase(Left\$(Word, 1))
strLastCode = GetSoundCodeNumber(strCode)
' Store the word length
lngWordLength = Len(Word)
' Continue from the second letter
For i = 2 To lngWordLength
strChar = GetSoundCodeNumber(UCase(Mid\$(Word, i, 1)))
' If adjacent numbers are the same, only count one of them
If Len(strChar) > 0 And strLastCode <> strChar Then
strCode = strCode & strChar
End If
strLastCode = strChar
Next
' Trim it down to a maximum of four characters...
Soundex = Left\$(strCode, 4)
' ... but if it's less than four characters, pad it out with a bunch of zeros...
If Len(strCode) < 4 Then
Soundex = Soundex & String\$(4 - Len(strCode), "0")
End If
End Function

Private Function GetSoundCodeNumber(Character As String) As String
'Accept a character and return the appropriate number from the Soundex table
Select Case Character
Case "B", "F", "P", "V"
GetSoundCodeNumber = "1"
Case "C", "

3. ## Re: Finding Dupe Qry w/ a Twist! (97)

Thanks HansV

This is something we are doing in the next 10 days or so.

I will try it and let you know how it works

4. ## Re: Finding Dupe Qry w/ a Twist! (97)

OK. How did it work?

5. ## Re: Finding Dupe Qry w/ a Twist! (97)

That was more than 5 years ago, John! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•