422,026 Members | 1,205 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,026 IT Pros & Developers. It's quick & easy.

Need Guidance Creating a SELECT Query

P: 3
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
Attached Images
File Type: jpg Join setup.jpg (31.0 KB, 9 views)
Attached Files
File Type: xlsx Qry_GeriMed A004.xlsx (12.2 KB, 18 views)
File Type: xlsx GMMemList.xlsx (21.2 KB, 18 views)
1 Week Ago #1
Share this Question
Share on Google+
8 Replies


twinnyfo
Expert Mod 100+
P: 1,968
Geogreco,

Welcome to Bytes!

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

Expand|Select|Wrap|Line Numbers
  1. 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.
1 Week Ago #2

gnawoncents
100+
P: 205
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.
1 Week Ago #3

P: 3
Thanks attached files for more information
1 Week Ago #4

twinnyfo
Expert Mod 100+
P: 1,968
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.
1 Week Ago #5

P: 3
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.
1 Week Ago #6

twinnyfo
Expert Mod 100+
P: 1,968
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.
1 Week Ago #7

Rabbit
Expert Mod 10K+
P: 12,170
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
1 Week Ago #8

PhilOfWalton
Expert 100+
P: 1,042
I don't know if this will help. It returns the similarity between two strings.

Expand|Select|Wrap|Line Numbers
  1. Public Function Simil(strTxt1 As String, strTxt2 As String) As Double
  2. 'Determine match percentage between two strings. between 0 (no match) en 1 (identical)
  3. '?simil("CHICKASHA CLINIC PHARMACY LLC", "CHICHESTERS INSTITUTIONAL PHARMACY")
  4.  
  5.     Dim intTot   As Integer
  6.     Dim strMatch As String
  7.     Dim Inti As Integer
  8.  
  9.     On Error GoTo Simil_Err
  10.  
  11.     If Mid(strTxt2, 1, Len(strTxt1)) = strTxt1 Then
  12.         Simil = 1
  13.         Exit Function
  14.     End If
  15.  
  16.     intTot = Len(strTxt1 & strTxt2)     'len(strtxt1) + len(strtxt2) 'Which is faster?
  17.  
  18.     strMatch = GetBiggest(strTxt1, strTxt2)
  19.  
  20.     Simil = CDbl(Len(strMatch) * 2) / CDbl(intTot)
  21.  
  22. Simil_Exit:
  23.     Exit Function
  24.  
  25. Simil_Err:
  26.     If Err = 9 Then                     ' Subscript out of range
  27.         Resume Next
  28.     Else
  29.         LogError Err, Err.Description, "Simil", Erl, Marker
  30.     End If
  31.  
  32. End Function
  33.  
  34. Public Function GetBiggest(strTxt1 As String, strTxt2 As String) As String
  35. 'Returnvalue is all matching strings
  36. '?GetBiggest("Pennsylvania","Pencilvaneya")
  37. 'lvanPena
  38.  
  39.     Dim intLang    As Integer
  40.     Dim intKort    As Integer
  41.     Dim intPos     As Integer
  42.     Dim intX       As Integer
  43.     Dim strLangste As String
  44.     Dim strSearch  As String
  45.     Dim strLang    As String
  46.     Dim strKort    As String
  47.     Dim strTotal1 As String
  48.     Dim strTotal2 As String
  49.  
  50.     intKort = Len(strTxt1)
  51.     intLang = Len(strTxt2)
  52.  
  53.     If intLang > intKort Then
  54.         strLang = strTxt2
  55.         strKort = strTxt1
  56.     ElseIf intKort = 0 Or intLang = 0 Then
  57.         Exit Function
  58.     Else
  59.         strLang = strTxt1
  60.         strKort = strTxt2
  61.         intX = intKort
  62.         intKort = intLang
  63.         intLang = intX
  64.     End If
  65.  
  66.     For intPos = 1 To intKort                           'Compare string based on the shortest.
  67.         intX = 0
  68.         Do
  69.             intX = intX + 1
  70.             strSearch = Mid$(strKort, intPos, intX)     'Determine part of string to search for
  71.             If Len(strSearch) <> intX Then
  72.                 Exit Do 'end of string
  73.             End If
  74.         Loop While InStr(1, strLang, strSearch) > 0     'Part of string found in other string, increase size of partstring and try again.
  75.         intX = intX - 1
  76.         If intX > Len(strLangste) Then                  'Longest substring found
  77.             strLangste = Mid$(strKort, intPos, intX)
  78.         End If
  79.         If intX = 0 Then intX = 1
  80.         intPos = intPos + intX - 1
  81.     Next intPos
  82.  
  83.     If Len(strLangste) = 0 Then
  84.         GetBiggest = ""                                 'No matching substring found
  85.     Else                                                'Substring match found.
  86.         'Split substring in left and right part.
  87.         strTotal1 = Replace(strTxt1, strLangste, "|")
  88.         strTotal2 = Replace(strTxt2, strLangste, "|")
  89.  
  90.         'Recursive part: Try again and paste result to returnvalue.
  91.         GetBiggest = strLangste & _
  92.             GetBiggest(CStr(Split(strTotal1, "|")(0)), CStr(Split(strTotal2, "|")(0))) & _
  93.             GetBiggest(CStr(Split(strTotal1, "|")(1)), CStr(Split(strTotal2, "|")(1)))
  94.     End If
  95.  
  96. End Function
  97.  
typing in
Expand|Select|Wrap|Line Numbers
  1. ?simil("CHICKASHA CLINIC PHARMACY LLC", "CHICHESTERS INSTITUTIONAL PHARMACY")
  2.  
in the immediate window gives a similarity of 0.571428571428571

whilst
Expand|Select|Wrap|Line Numbers
  1. ?simil("SIMPLE MEDS", "SIMPLE MEDS, LLC")
  2.  
gives a similarity of 1

Phil
1 Week Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.