By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,117 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,117 IT Pros & Developers. It's quick & easy.

How do I get a partial match?

P: 4
I have been working on this for a while now and I wanted to see if someone could assist me. I have 2 tables each with 5 fields. 4 of the fields are 2 character strings, then the final field is the full value.
[EX 1:
Table1................Table2
Seg1: 10.............Seg1: 10
Seg2: 11.............Seg2: 11
Seg3: 15.............Seg3: 15
Seg4: 01.............Seg4: Null
Final: 10111501...Final: 101115]
[Ex 2:
Table1................Table2
Seg1: 10.............Seg1: 10
Seg2: 11.............Seg2: 11
Seg3: 15.............Seg3: 15
Seg4: 01.............Seg4: 01
Final: 10111501...Final: 10111501]


I know how to match the finals where they are equal but I need to match where there is a partial match with the greatest number of Segments. So in the above I would need to exclude the exact match EX 2 and match 10111501 to 101115 like in EX 1, however I have not been able to get this to work.

Any assistance would be greatly appreciated.
Aug 17 '09 #1
Share this Question
Share on Google+
16 Replies


Expert 100+
P: 1,287
This sounds very much like an assignment, but are you matching one record from Table1 with a specific record in Table2, or all/any of them? Are you trying to do this in a query?
Aug 17 '09 #2

Expert 100+
P: 266
I'm not sure I know exactly what you are asking, you may need to clarify more. But if I were to guess, you could compare the fields, using a IIF function and a LEN function.

example:
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.Seg1, Table1.Seg2, Table1.Seg3, Table1.Seg4, Table1.Seg5
  2. FROM Table1, Table2
  3. WHERE (((IIf(Len([Table1].[Seg5])>Len([Table2].[Seg5]),[Table2].[Seg5]=Left([Table1].[Seg5],Len([Table2].[Seg5])),[Table1].[Seg5]=Left([Table2].[Seg5],Len([Table1].[Seg5]))))<>False));
  4.  
The code above, compares the length of both fields, then based on which field is longer, it will cut the longer field down to the same length of the smaller field, and then do an equal join on the 2 fields.

Let me know if this makes sense,
-AJ
Aug 17 '09 #3

P: 4
AJ,

Thanks for the information. I am not sure this will work for what I need though. Basically I have 2 tables 1 may have 10111501 and the other may have 101115 without the last segment. I am trying to figure out a way to do this that is efficient and quick. I was able to do it with Sub queries but they take forever to run.

Let e know if this makes sense to you.

-James
Aug 19 '09 #4

ADezii
Expert 5K+
P: 8,627
@vorlonfear
You may wish to try the Instr() Function which will specifying the position of the first occurrence of one string within another.
Expand|Select|Wrap|Line Numbers
  1. Debug.print InStr(10111501,101115) 
will equal 1 indicating that 101115 is contained withing 10111501 starting at Position 1.
Expand|Select|Wrap|Line Numbers
  1. Debug.print InStr(10111501,102115 
will evaluate to 0 since 102115 is not contained in 10111501.
Aug 19 '09 #5

P: 4
ADezii,

I thought about using Instr() but There may be multiple hits for a single match. Let me give another example.

Table 1: Has 10111501
Table 2: Has 10, 1011, 101115

I would need to match 10111501 to 101115 but not 10 or 1011.

Thanks for the help.
Aug 19 '09 #6

Expert 100+
P: 266
How about trying the InStr() under an if that checks the length of the string first

Example:
Expand|Select|Wrap|Line Numbers
  1. If len(101115) > 4 then
  2.    Debug.print InStr(10111501,101115)
  3. End If 
  4.  
-AJ
Aug 19 '09 #7

P: 4
The only problem is that there are thousands of codes that need to be matched up.
Aug 19 '09 #8

Expert 100+
P: 266
One way or another you will need a function to handle each value as it is passed through the function, have you tested this function and see how long it takes? Because you would be surprised how fast some functions work, it all depends on the intensity of the function.
I once wrote a strip string function that i swore would take forever on 500,000 records but once I ran it, it was faster than I had expected it to be.
I welcome anyone with a better idea, but I'm not sure if there is a much better one.

-AJ
Aug 19 '09 #9

NeoPa
Expert Mod 15k+
P: 31,429
If, as seems clear, you will need to determine which of the pairs (of records from the two tables) has the best match, then you will need to produce a cartesian product of matches before grouping these and finding the greatest number of sub-field matches.

This will certainly take an enormous amount of processing. The larger the individual tables grow, the longer this will take (not even gradually, but exponentially).

I'm sorry - I see no good news here.

Unless you can change your specification somehow, so that there are some givens to work with and set the indices to, I see no viable solution.
Aug 19 '09 #10

ADezii
Expert 5K+
P: 8,627
@vorlonfear
Before I attempt an Algorithm to solve this problem efficiently, I want to be absolutely sure that I am interpreting it correctly. For each Record in each Table, you have to concatenate each of the 5 Fields and compare the Final Results to each other to see if there is a Match. Is this correct?
Aug 19 '09 #11

Expert 100+
P: 1,287
If I'm understanding the problem, I would take the cross product of the 2 tables, add a calculated field for
Expand|Select|Wrap|Line Numbers
  1. Matches: iif(table1.seg1 = table2.seg1, 1, 0) + 
  2.          iif(table1.seg2 = table2.seg2, 1, 0) + 
  3.          iif(table1.seg3 = table2.seg3, 1, 0) + 
  4.          iif(table1.seg4 = table2.seg4, 1, 0)
Then take the record with Max(Matches). Hopefully you can just take one record from the first table and match it against every record in the second, rather than the expensive full cross product.
Aug 19 '09 #12

NeoPa
Expert Mod 15k+
P: 31,429
I think you have it Chip (although Seg1 = Seg1 rather than Seg1 = Seg2 - Just a typo I'm sure).

Your last paragraph doesn't make too much sense though (As far as I can see). For each record from Table1 there should be a reference to each record in Table2. If only 1 record were processed from Table1, then only one record would have a match found. May make sense when processing in code, but I don't see it for a query.

To restrict the links from processing a full cartesian product though, each of the sub-fields could be set as an index (one each) and selection criteria set such that only records where at least one of the indices found a match would be processed. This is easy enough to specify with ORed criteria.
Aug 19 '09 #13

Expert 100+
P: 1,287
I was thinking something like
WHERE table1.recordID = Forms![InputForm]!cmbRecordID
Aug 19 '09 #14

NeoPa
Expert Mod 15k+
P: 31,429
@ChipR
That makes some sense. Actually, depending on the OP's requirements of course, that could be it.

PS. Please see paragraph I added to my earlier post. You're obviously very sharp tonight with your responses ;)
Aug 19 '09 #15

P: 16
Try using the keyword "like" in your query.

"WHERE table1.recordID Like "*" Forms![InputForm]!cmbRecordID "*" "

May be that's what you are looking for.
Aug 20 '09 #16

NeoPa
Expert Mod 15k+
P: 31,429
@tkip
I see where you're going, but I don't think this would work in the current situation.

We always welcome any and all attempts to help out though - Welcome to Bytes!
Aug 20 '09 #17

Post your reply

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