Need Guidance Creating a SELECT Query

[Geogreco]

I have 2 tables and need to create a query that pulls the names that match from each name field. The problem is when i created a join of course it works when the whole name matches but need it to pull when only the first and second name matches. See example.

SIMPLE MEDS
SIMPLE MEDS, LLC

[twinnyfo]

Geogreco,

Welcome to Bytes!

You would need to join based on the first part of each field. For example:

Left([FieldName], 11)

But, this can cause problems, because you don't know for sure how long your field names are. Without knowing more about your data set or what you are trying to accomplish in the long term, We will need more information in order to give a more definite answer.

If you can provide more information on your purpose for this join, we can perhaps provide more assistance.

[gnawoncents]

Will there always be a comma after the first and second name? Or, rather, always a comma when there is a third name? Like twinnyfo said, more information will get you more useful responses.

[Geogreco]

Thanks attached files for more information

[twinnyfo]

Geogreco,

Thanks for the files, but this merely confirmed what we already knew: that some of your names are mismatched in one table compared to the other.

What are you trying to accomplish? Is this a one-time import to match your customer names to a list of names? Or is this going to have to be a permanent join of tables?

If what you are looking for is permanent, one possible solution is to create a third table with two fields: one with the Customer Name and the other field the Pharmacy Name. Then simply match the two names together (this would have to be done manually). Then your join would have this table in between your two main tables. That is not the most perfect of situations, but it would work.

What you really want is normalized tables that would prevent this.

[Geogreco]

The objective of this is to find members from the membership file provided from membership at Gerimed to our file which was created from central membership, unfortunately the people who creates the membership files take liberties with creating the names, mostly due to laziness!. So errors get generated when a distributor of our products submit rebates for customers against the contract for Gerimed that are not on contract. In short i need to run a query for members that get submitted from Distributors and see if they are truly on the Gerimed roster.

[twinnyfo]

Ahhh, then you need to control how your users select the vendors--through DB Normalization. If anything, they should be selecting those vendors from your main list of pharmacies, using an indexed table. This would prevent them from ever entering a bad name.

This is a DB/Table restructure issue, and perhaps a bit outside the scope of your original question, but is at the heart of the real solution to your problem.

[Rabbit]

There's no way to get 100% accurate matching when you only want to look at portions of the string. But there are various fuzzy string matching approaches that you can take.

The simplest would twinnyfo's suggestion of looking at just the first x characters to see if they match.

There are also other approaches such as:
Double Metaphone
Levenshtein
NGrams

[PhilOfWalton]

I don't know if this will help. It returns the similarity between two strings.

Public Function Simil(strTxt1 As String, strTxt2 As String) As Double
'Determine match percentage between two strings. between 0 (no match) en 1 (identical)
'?simil("CHICKASHA CLINIC PHARMACY LLC", "CHICHESTERS INSTITUTIONAL PHARMACY")
 
    Dim intTot   As Integer
    Dim strMatch As String
    Dim Inti As Integer
 
    On Error GoTo Simil_Err
 
    If Mid(strTxt2, 1, Len(strTxt1)) = strTxt1 Then
        Simil = 1
        Exit Function
    End If
 
    intTot = Len(strTxt1 & strTxt2)     'len(strtxt1) + len(strtxt2) 'Which is faster?
 
    strMatch = GetBiggest(strTxt1, strTxt2)
 
    Simil = CDbl(Len(strMatch) * 2) / CDbl(intTot)
 
Simil_Exit:
    Exit Function
 
Simil_Err:
    If Err = 9 Then                     ' Subscript out of range
        Resume Next
    Else
        LogError Err, Err.Description, "Simil", Erl, Marker
    End If
 
End Function
 
Public Function GetBiggest(strTxt1 As String, strTxt2 As String) As String
'Returnvalue is all matching strings
'?GetBiggest("Pennsylvania","Pencilvaneya")
'lvanPena
 
    Dim intLang    As Integer
    Dim intKort    As Integer
    Dim intPos     As Integer
    Dim intX       As Integer
    Dim strLangste As String
    Dim strSearch  As String
    Dim strLang    As String
    Dim strKort    As String
    Dim strTotal1 As String
    Dim strTotal2 As String
 
    intKort = Len(strTxt1)
    intLang = Len(strTxt2)
 
    If intLang > intKort Then
        strLang = strTxt2
        strKort = strTxt1
    ElseIf intKort = 0 Or intLang = 0 Then
        Exit Function
    Else
        strLang = strTxt1
        strKort = strTxt2
        intX = intKort
        intKort = intLang
        intLang = intX
    End If
 
    For intPos = 1 To intKort                           'Compare string based on the shortest.
        intX = 0
        Do
            intX = intX + 1
            strSearch = Mid$(strKort, intPos, intX)     'Determine part of string to search for
            If Len(strSearch) <> intX Then
                Exit Do 'end of string
            End If
        Loop While InStr(1, strLang, strSearch) > 0     'Part of string found in other string, increase size of partstring and try again.
        intX = intX - 1
        If intX > Len(strLangste) Then                  'Longest substring found
            strLangste = Mid$(strKort, intPos, intX)
        End If
        If intX = 0 Then intX = 1
        intPos = intPos + intX - 1
    Next intPos
 
    If Len(strLangste) = 0 Then
        GetBiggest = ""                                 'No matching substring found
    Else                                                'Substring match found.
        'Split substring in left and right part.
        strTotal1 = Replace(strTxt1, strLangste, "|")
        strTotal2 = Replace(strTxt2, strLangste, "|")
 
        'Recursive part: Try again and paste result to returnvalue.
        GetBiggest = strLangste & _
            GetBiggest(CStr(Split(strTotal1, "|")(0)), CStr(Split(strTotal2, "|")(0))) & _
            GetBiggest(CStr(Split(strTotal1, "|")(1)), CStr(Split(strTotal2, "|")(1)))
    End If
 
End Function
 

typing in

?simil("CHICKASHA CLINIC PHARMACY LLC", "CHICHESTERS INSTITUTIONAL PHARMACY")
 

in the immediate window gives a similarity of 0.571428571428571

whilst

?simil("SIMPLE MEDS", "SIMPLE MEDS, LLC")
 

gives a similarity of 1

Phil