Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    I thought about using Address One in conjunction w/ 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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    OK. How did it work?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •