473,903 Members | 4,817 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Matching field by 5 first characters

30 New Member
I am trying to match tables into a query based on 5 first characters in one field. I used expression: =left([field name]), 5) however it does not return matched entries as I needed. Basically, I need to tell query match two fields based on first characters and and show all records from Table 1 and only those records from table 2 where first 5 letters of the fields are matching.

Here is my SQL from the query:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Master - Account by SVP Consolidated].[Scode Description], Master_Missing_Fields.Account, [Master - Account by SVP Consolidated].[Ops SVP], [Master - Account by SVP Consolidated].[AM SVP (Global)]
  2. FROM Master_Missing_Fields LEFT JOIN [Master - Account by SVP Consolidated] ON (Master_Missing_Fields.Account =left([Master - Account by SVP Consolidated].[Scode Description], 5));
Mar 15 '11
21 12059
12,516 Recognized Expert Moderator MVP
What's the size of the dataset? If it's imperative to match all records, your only sure fire method is manually going through and matching it.

If you're just trying to match as many as possible, then there are lots of algorithms. You could use a simple one in which you check on a word by word basis. This wouldn't give many positive matches.

If you want as many positive matches as you can, I think your best bet is an n-gram algorithm. But beware that it's a fairly advanced algorithm and would require a lot of work to implement correctly. Unless, of course, you could find an existing library.
Mar 16 '11 #11
8,834 Recognized Expert Expert
@Yoni - What if the following Scenario comprised a Match (<==>):
  1. [Scode Description] and [Acct Name] are equal:
    1. Eliminations <==> Eliminations
    2. DBank <==> Dbank
    3. East Facilities <==> East Facilities
    4. Global North America <==> Global North America
  2. Greater than 50% of the Components in [Scode Description] are also contained in [Acct Name] in some manner:
    1. East Facilities <==> FacilitiesEast (both East and Facilites are contained in FacilitiesEast (100%), but Facilities would not be a Match since only Facilities in East Facilities in contained within (50%).
    2. Southfield Facilities ==> No Match in [Acct Name], since both Components are not contained within the [Acct Name].
    3. Global North America <==> North America (both North and America contained in [Acct Name] 66.6%.
P.S. - I do realize that this approach is rather simplistic, but the only other alternative as I see it is what Rabbit pointed out, more involved Algorithms. Matches could be written to a Results Table consisting of [Scode Description], [Acct Name], and [Ops SVP].
Mar 16 '11 #12
Yoni Hasid
30 New Member
I think your second approach may work for my purpose. Would you by chance have a code that I can analyze and possibly use for this query?
Mar 16 '11 #13
8,834 Recognized Expert Expert
@Yoni - To whom are you referring to, Rabbit or ADezii, in your last Post?
Mar 16 '11 #14
12,516 Recognized Expert Moderator MVP
I think he's talking to you ADezii. He must have mistook your #2 as an alternate approach rather than a complementary approach.

Yoni, since the fields are free form, what do you intend to do about mispellings, typos, abbreviations, etc?
Mar 16 '11 #15
Yoni Hasid
30 New Member
Thanks to all. I was replying to ADezii.
Mar 16 '11 #16
8,834 Recognized Expert Expert
@Yoni - I'll come up with something, but it will be by no means comprehensive. To be honest, I do not think that there is a comprehensive solution to your rather unique situation. Give me some time, and I'll see what I can come up with. The results will be displayed in an independent Table, and not in a Query per say, since I do not see an easy Method to incorporate the Logic into a Query. Stay tuned...
Mar 16 '11 #17
8,834 Recognized Expert Expert
@Yoni - Here is an Algorithm that I created based on what I previously stated in Post #12. I'll post the Code, which is well commented, as well as the Demo Database used for this Thread. Should you have any questions, please feel free to ask. You can make the Results more or less Restrictive by changing the Value of the conPERCENTAGE Constant, as well as the Relational Operator that proceeds it in Code Line #43.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst_1 As DAO.Recordset              'Table SVP_Account_Name
  3. Dim rst_2 As DAO.Recordset              'Table Account_Name_Incomplete
  4. Dim rstResults As DAO.Recordset         'Results Table
  5. Dim varRet                              'Variant Array to hold Elements of [Scode Description]
  6. Dim intCtr As Integer                   'Used to Loop thru Elements of [Scode Description]
  7. Dim intNumOfMatches As Integer          'Number of Matches (Elements in [Scode Description]
  8. Dim intOverallCtr As Integer            'Overall Counter (Number of Elements in [Scode Description])
  9. Const conPERCENTAGE As Single = 0.5     '> this Value on [Scode Description] Elements indicates Match
  11. 'Clear the Results Table
  12. CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError
  14. Set MyDB = CurrentDb()
  15. Set rst_1 = MyDB.OpenRecordset("SVP_Account_Name", dbOpenForwardOnly)
  16. Set rst_2 = MyDB.OpenRecordset("Account_Name_Incomplete", dbOpenSnapshot)
  17. Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset, dbAppendOnly)
  19. 'Match every Record ([Scode Description]] in SVP_Account_Name against every
  20. 'Record ([Acct Name])Record in Account_Name_Incomplete for any Partial Matches
  21. With rst_1
  22.   Do While Not .EOF
  23.     Do While Not rst_2.EOF
  24.       'Check for exact Matches first (Case-insensitive)
  25.       If ![Scode Description] = rst_2![Acct Name] Then
  26.         rstResults.AddNew
  27.           rstResults![scode] = ![Scode Description]
  28.           rstResults![Acct Name] = rst_2![Acct Name]
  29.           rstResults![Ops SVP] = ![Ops SVP]
  30.         rstResults.Update
  31.         'Debug.Print ![Scode Description], rst_2![Acct Name], ![Ops SVP]
  32.       Else      'No Match, parse [Scode Description], and see if > 50% of its
  33.                 'Elements are contained within [Acct Name]. In a prior Post, yoou
  34.                 'indicated that Spaces should be ignored, so let's account for that also
  35.         varRet = Split(![Scode Description], " ")
  36.           For intCtr = LBound(varRet) To UBound(varRet)
  37.             intOverallCtr = intOverallCtr + 1
  38.               If InStr(Replace(rst_2![Acct Name], " ", ""), varRet(intCtr)) > 0 Then
  39.                 intNumOfMatches = intNumOfMatches + 1
  40.               End If
  41.           Next
  42.             'Do > 50% of Elements in [Scode Description] match [Acct Name]
  43.             If (intNumOfMatches / intOverallCtr) > conPERCENTAGE Then    'Percentage Criteria for a 'MATCH'
  44.               rstResults.AddNew
  45.                 rstResults![scode] = ![Scode Description]
  46.                 rstResults![Acct Name] = rst_2![Acct Name]
  47.                 rstResults![Ops SVP] = ![Ops SVP]
  48.               rstResults.Update
  49.               'Debug.Print ![Scode Description], rst_2![Acct Name], ![Ops SVP]
  50.             End If
  51.             intNumOfMatches = 0: intOverallCtr = 0      'RESET, critical
  52.       End If
  53.         rst_2.MoveNext
  54.     Loop
  55.         rst_2.MoveFirst
  56.        .MoveNext
  57.   Loop
  58. End With
  60. rst_1.Close
  61. rst_2.Close
  62. rstResults.Close
  63. Set rst_1 = Nothing
  64. Set rst_2 = Nothing
  65. Set rstResults = Nothing
  67. 'Let's see the Results
  68. DoCmd.OpenTable "tblResults", acViewNormal, acReadOnly
  69. DoCmd.Maximize
Results Table (tblResults) from Data submitted in Post #9 via Attachment, plus one Test Record indicating Space elimination:
Expand|Select|Wrap|Line Numbers
  1. SCode                    Acct Name                 Ops SVP
  2. DBank                    Dbank                     Teri Muller
  3. East Facilities          East Facilities           Kathy Wu
  4. East Facilities          FacilitiesEast            Kathy Wu
  5. Eliminations             Eliminations              Jorge Carr
  6. Global North America     Global North America      Betty Dunn
  7. Global North America     North America             Betty Dunn
  8. Help Me Rhonda           Rh onda help              ADezii
Attached Files
File Type: zip Find Matches.zip (19.1 KB, 183 views)
Mar 16 '11 #18
12,516 Recognized Expert Moderator MVP
Just a suggestion but you could generalize the function to the form:
Expand|Select|Wrap|Line Numbers
  1. Function matchPercentage(str1 As String, str2 As String) As Double
  2.    ...
  3.    matchPercentage = numMatches / numTerms
  4. End Function
Then you can call it from a SQL query.
Mar 16 '11 #19
8,834 Recognized Expert Expert
Thanks Rabbit for the suggestion. Actually, the Code is too long to be integrated into a Single Procedure anyway. I intentionally kept it in-line for clarification reasons, but in hindsight, it should probably have been splintered into two or more routines. I know that the Code is a little rogue, OK a lot rogue, but what is your honest opinion on this particular approach?
Mar 17 '11 #20

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

Similar topics

by: Synonymous | last post by:
Hello, Can regular expressions compare file names to one another. It seems RE can only compare with input i give it, while I want it to compare amongst itself and give me matches if the first x characters are similiar. For example: cccat
by: Dan Jones | last post by:
I have a database of books that was originally created as a flat file. Each record has a number of fields, including the authors name. I'm trying to convert the database to something a little more efficient. I've created a new table (called Authors) of unique authors names and assigned each one a unique ID. I've added a new field in the original table (called Books) for the author's ID. Now, I need to update the original table with the...
by: Dejan M. | last post by:
Please can you help me with this problem: In the next form, when I Add new person who is not registered in the base I had the massage: The Microsoft Jet database engine cannot find a record in the table 'CLAN' with key matching field(s) JMBG. The question is: How to add data for the unregistered visitors in the next fields: JMBG (unique person number);(translation) STATUS;
by: .Net Sports | last post by:
I've been looking everywhere, but i can't locate any functions, or articles on the net or my resources, on how to open a file using vb.net, and then grabbing certain parts of the file, and putting it into a useable variable. I know there are functions like Split, Len, Trim, etc., but I have found nothing as far as the sequence for the syntax. I am interested in grabbing the first line, or first 50 characters for example, and then being...
by: Henrootje | last post by:
Hello all, I have a problem that is alike others I have seen but I am not able to apply the solutions I saw using Trim, Left, Right and so on. That is why I ask here. What is the case? I have a variable 'OpenArgs' that can be filled with several values. They have in common that they all start with 'rpt' (eg;
by: Dawn | last post by:
Can I have a criteria where, when I add new records only, that data must be entered in field A before data is entered in Field B? So if a user goes to field B and starts entering data before data has been entered in field A an error message pops up telling him/her to enter data in Field A first. Is this possible and is this bad design? Thank you for your time. Dawn
by: Petepinca | last post by:
I have 2 Access tables. One has a field with a 4 character product id. Example data: "3210". The other table has the same product id's for matching records BUT has leading characters: "RD3210", etc. I want to match records in an Access query that will match the fields based on the last 4 characters, which fortunately, are unique in both tables. I can edit in SQL View if necessary. Thanks for any help.
by: Dean | last post by:
Hi, I have a table with non-unique identifiers. I need to take all the values with the same ID's and combine them into one field with a semicolon as a seperator. These values may exceed 255 characters. I then need to count the values in the cell and see if it adds up to 240 or more and then shorten that field by cutting off the excess and adding only "...and other". So, to recap, I need to summarise values based on the ID field, count...
by: nofear | last post by:
I have 2 tables with 1 field each the filed of the fist table has a 3 digit number stored as text and the field of the second table has a 6 digit number stored as text I'd like to select each 6 digit number where the first 3 digits match the field in the first table I'm not sure If I can use the "LIKE"
by: danon | last post by:
Hi evrey one needing you're hlp please can any one tel me how to programme a first characters uppercase in textbox thank you
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.