X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Compare 2 Fields That May Not Be An Exact Match, Access 2010    
post Sep 23 2019, 06:39 AM

Posts: 186
Joined: 13-August 04

Good afternoon,
i have 2 tables which contain thousands of records based on the customers name. The records in table one are in the correct format (Forename, Surname) However the 2nd table the data is different (Surname, Forename or the Forename, Surname) so i cant just switch the data around.

i need to be able to create a query that checks to see if that a set number of characters in table one is replicated somewhere in the field in table 2

Table 1 Table 2
James Walkinshaw Walkinshaw James

so the query would look for say 5 char and providing the same 5 char were in both tables in the same order it would return a likely match.

hope that makes sense.
Go to the top of the page
post Sep 23 2019, 07:02 AM

UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


Dealing with name matching is a major headache (which is one of a number of reasons we try not to rely on them as "controlled data" that we ID with, but sometimes there's just no getting around it: particularly when importing from other systems).

Anyway, if this swap is all you have to worry about, it's relatively easy.

I would create a VBA function to call from the query that will split the name into an array based on the space, then run an element comparison:

Dim a as variant
Dim b as variant

a = split(value1, " ")
b = split(value2, " ")

if (a(0) = b(0) and a(1) = b(1)) or (a(0) = b(1) and a(1) = b(0)) then 'likely a match

Be aware that this may return some false positives, but should get you pretty close.

(this does nothing for mispellings and potential other info: nicknames, ordinals, jr/sr, etc - that's where things really start to get ugly)


Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
post Sep 23 2019, 07:04 AM

Posts: 368
Joined: 2-April 18

Surname and Forename are in the same field, aren't they?

Do you have a special character (comma etc) that separate Surname from Forename?

Could there be different customers with the same name?

Please forgive in advance my horrible English.
Go to the top of the page
post Sep 23 2019, 08:02 AM

Posts: 186
Joined: 13-August 04

yes surname and forename are in the same field table A is not separated by a comma but table B some records are separated
Go to the top of the page
post Sep 23 2019, 08:10 AM

Posts: 1,510
Joined: 2-April 09
From: somewhere out there...

copy this 2 functions in a Module
' arnelgp
Public Function swapFirstLastName(ByVal s As Variant, ByVal bolSwap As Boolean) As String
    If IsEmpty(s) Then Exit Function
    s = Trim(Replace(s, ",", ""))
    s = RegExpReplace(s, "[^A-Za-z0-9]","")
    s = RegExpReplace(s, "[\ ]{1,}", " ")
    If InStr(1, s, " ") > 0 Then
        If bolSwap Then
            s = Trim(Split(s, " ")(1)) & Trim(Split(s, " ")(0))
            s = Trim(Split(s, " ")(0)) & Trim(Split(s, " ")(1))
       End If
    End If
    swapFirstLastName = s
End Function

' arnelgp
Public Function RegExpReplace(ByVal WhichString As String, _
        ByVal Pattern As String, _
        ByVal ReplaceWith As String, _
        Optional ByVal IsGlobal As Boolean = True, _
        Optional ByVal IsCaseSensitive As Boolean = True) As String
    With CreateObject("vbscript.regexp")
        .Global = IsGlobal
        .Pattern = Pattern
        .IgnoreCase = Not IsCaseSensitive
        RegExpReplace = .Replace(WhichString, ReplaceWith)
    End With
End Function

your goal is to return records that has matching firstname lastname or lastname firstname.
create a query:
select table1.[name], table2.[name]  from table1, table2 where
         swapFirstLastName(table1.[name], False) = swapFirstLastName(table2.[name], True) Or
         swapFirstLastName(table1.[name], False) = swapFirstLastName(table2.[name], False)

This post has been edited by arnelgp: Sep 23 2019, 08:11 AM

Never stop learning, because life never stops teaching.
Go to the top of the page
post Sep 23 2019, 10:42 AM

Posts: 186
Joined: 13-August 04

thank you
Go to the top of the page
post Sep 23 2019, 11:01 AM

Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach

Are you attempting to find a match(es) on Name components or consecutive Character Matches? If it is on consecutive Character Matches, then a Character Match Count of 5 would indicate a Match on 'Stein' below:
John Stein <==> Walter Frankenstein

This post has been edited by ADezii: Sep 23 2019, 11:02 AM
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    13th December 2019 - 04:58 AM