By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 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.

Attached Images
File Type: jpg Join setup.jpg (31.0 KB, 69 views)
Attached Files
File Type: xlsx Qry_GeriMed A004.xlsx (12.2 KB, 94 views)
File Type: xlsx GMMemList.xlsx (21.2 KB, 109 views)
Feb 12 '18 #1
Share this Question
Share on Google+
8 Replies

Expert Mod 2.5K+
P: 3,284

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.
Feb 12 '18 #2

P: 214
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.
Feb 13 '18 #3

P: 3
Thanks attached files for more information
Feb 13 '18 #4

Expert Mod 2.5K+
P: 3,284

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.
Feb 13 '18 #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.
Feb 13 '18 #6

Expert Mod 2.5K+
P: 3,284
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.
Feb 13 '18 #7

Expert Mod 10K+
P: 12,366
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
Feb 15 '18 #8

Expert 100+
P: 1,430
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)
  5.     Dim intTot   As Integer
  6.     Dim strMatch As String
  7.     Dim Inti As Integer
  9.     On Error GoTo Simil_Err
  11.     If Mid(strTxt2, 1, Len(strTxt1)) = strTxt1 Then
  12.         Simil = 1
  13.         Exit Function
  14.     End If
  16.     intTot = Len(strTxt1 & strTxt2)     'len(strtxt1) + len(strtxt2) 'Which is faster?
  18.     strMatch = GetBiggest(strTxt1, strTxt2)
  20.     Simil = CDbl(Len(strMatch) * 2) / CDbl(intTot)
  22. Simil_Exit:
  23.     Exit Function
  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
  32. End Function
  34. Public Function GetBiggest(strTxt1 As String, strTxt2 As String) As String
  35. 'Returnvalue is all matching strings
  36. '?GetBiggest("Pennsylvania","Pencilvaneya")
  37. 'lvanPena
  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
  50.     intKort = Len(strTxt1)
  51.     intLang = Len(strTxt2)
  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
  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
  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, "|")
  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
  96. End Function
typing in
Expand|Select|Wrap|Line Numbers
in the immediate window gives a similarity of 0.571428571428571

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

Feb 15 '18 #9

Post your reply

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