How do I get a partial match?

[vorlonfear]

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...Fina l: 101115]
[Ex 2:
Table1......... .......Table2
Seg1: 10............. Seg1: 10
Seg2: 11............. Seg2: 11
Seg3: 15............. Seg3: 15
Seg4: 01............. Seg4: 01
Final: 10111501...Fina l: 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.

[ChipR]

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?

[ajalwaysus]

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:

SELECT Table1.Seg1, Table1.Seg2, Table1.Seg3, Table1.Seg4, Table1.Seg5
FROM Table1, Table2
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));
 

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

[vorlonfear]

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

[ADezii]

@vorlonfear
You may wish to try the Instr() Function which will specifying the position of the first occurrence of one string within another.

Debug.print InStr(10111501,101115) 

will equal 1 indicating that 101115 is contained withing 10111501 starting at Position 1.

Debug.print InStr(10111501,102115 

will evaluate to 0 since 102115 is not contained in 10111501.

[vorlonfear]

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.

[ajalwaysus]

How about trying the InStr() under an if that checks the length of the string first

Example:

If len(101115) > 4 then
   Debug.print InStr(10111501,101115)
End If 
 

-AJ

[vorlonfear]

The only problem is that there are thousands of codes that need to be matched up.

[ajalwaysus]

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

[NeoPa]

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.