Partial Matches-Access

[JWeezy]

I am trying to find duplicates within 1 table. The field is Invoice #. I have partial matches such as "124 376" and "124376" The other one is "MW4967" and "MW04967EX". How is the best way to find these duplicates by running a query? Any help is greatly appreciated!

 

[MajP]

Without artificial intelligence there is no query in the world that would know that MW04967EX and MW4967 is the same invoice# or a differetn invoice#. So what you need is a strategy and that strategy should focus on the common type of mistakes.

So I would have lots of queries to identify possible problems. Since you probably know the likely problems, focus on those rules first.

You showed two possible rules.
1) like records with spaces.
So for that problem I would make a simple function

Public Function removeSpaces(strInvoice As String) As String
removeSpaces = Replace(strInvoice, " ", "")
End Function

Then use that in a query

SELECT
removeSpaces([tblA.invoice#]) AS NoSpaceA,
tblB.[invoice#]
FROM
tblA,
tblB
WHERE
removeSpaces([tblA.invoice#])=removeSpaces([tblB.invoice#])

2) Maybe if all the numeric part is the same I check that

Public Function MakeNumeric(strText As String) As Long
    If strText = vbNullString Then MakeNumeric = 0
    Dim Characters(10) As String, i As Integer, sTemp As String
    
    If Len(strText) > UBound(Characters) Then
        strText = Left(strText, UBound(Characters))
    End If
    For i = 1 To Len(strText)
        Characters(i - 1) = Right(Left(strText, i), 1)
    Next
    For i = 0 To UBound(Characters)
        If Not Characters(i) = vbNullString Then
            If IsNumeric(Characters(i)) = True And Not Characters(i) = Chr(32) Then
                sTemp = sTemp & Characters(i)
            End If
        End If
    Next
   MakeNumeric = sTemp
End Function

SELECT
makeNumeric([tblA.invoice#]) AS NumericA,
tblB.[invoice#]
FROM
tblA,
tblB
WHERE makeNumeric([tblA.invoice#]))=makeNumeric([tblB.invoice#])

3) now I could build a function "makeAlpha" and check if the non numeric part is the same
4) then I might want to check if the left N characters math the left N characters in B

Public Function leftX(strInvoice As String, X As Integer) As String
  leftX = Left(strInvoice, X)
End Function
Public Function rightX(strInvoice As String, X As Integer) As String
  rightX = Right(strInvoice, X)
End Function

SELECT
tblA.[invoice#],
tblB.[invoice#]
FROM
tblA,
tblB
WHERE
tblB.[invoice#]) Like leftx([tblA.invoice#],4) & "*")) OR (((tblA.[invoice#]) Like leftx([tblb.invoice#],3) & "*"


Get the general strategy? You can probably define better rules because you know the likely errors.

 

[MajP]

you could even use a Soundex function in a query to test if the strings sound alike

' Computes the "Soundex" value of a string.
' This version produces exactly the same results as the Soundex
' function of Microsoft SQL Server 2000.
' Author: Christian d'Heureuse, chdh@source-code.biz
Public Function Soundex2(ByVal s As String) As String
   Const CodeTab = " 123 12  22455 12623 1 2 2"
   '                abcdefghijklnmopqrstuvwxyz
   If Len(s) = 0 Then Soundex2 = "0000": Exit Function
   Dim c As Integer
   c = Asc(Mid$(s, 1, 1))
   If c >= 65 And c <= 90 Or c >= 97 And c <= 122 Then
      ' nop
    ElseIf c >= 192 And c <= 214 Or c >= 216 And c <= 246 Or c >= 248 Then
      ' nop
    Else
      Soundex2 = "0000"
      Exit Function
      End If
   Dim ss As String, PrevCode As String
   ss = UCase(Chr(c))
   PrevCode = "?"
   Dim p As Integer: p = 2
   Do While Len(ss) < 4 And p <= Len(s)
      c = Asc(Mid(s, p))
      If c >= 65 And c <= 90 Then
         ' nop
       ElseIf c >= 97 And c <= 122 Then
         c = c - 32
       ElseIf c >= 192 And c <= 214 Or c >= 216 And c <= 246 Or c >= 248 Then
         c = 0
       Else
         Exit Do
         End If
      Dim Code As String: Code = "?"
      If c <> 0 Then
         Code = Mid$(CodeTab, c - 64, 1)
         If Code <> " " And Code <> PrevCode Then ss = ss & Code
         End If
      PrevCode = Code
      p = p + 1
      Loop
   If Len(ss) < 4 Then ss = ss & String$(4 - Len(ss), "0")
   Soundex2 = ss
   End Function
[code]

This can be used in a query to see if
"John Smith" or "Jon Smyth" exist

 

[JWeezy]

Thanks thats a great start!

How would I write it so it can delete all the letters and spaces at the same time?

Thanks

 

[MajP]

The make numeric function does that already. You would actually have to modify the code to leave the spaces

?makeNumeric("123 456 ABC")
= 123456