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. 16 5164
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?
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
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
@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.
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.
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
The only problem is that there are thousands of codes that need to be matched up.
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 32,556
Expert Mod 16PB
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.
@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?
If I'm understanding the problem, I would take the cross product of the 2 tables, add a calculated field for - Matches: iif(table1.seg1 = table2.seg1, 1, 0) +
-
iif(table1.seg2 = table2.seg2, 1, 0) +
-
iif(table1.seg3 = table2.seg3, 1, 0) +
-
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.
NeoPa 32,556
Expert Mod 16PB
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.
I was thinking something like
WHERE table1.recordID = Forms![InputForm]!cmbRecordID
NeoPa 32,556
Expert Mod 16PB @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 ;)
Try using the keyword "like" in your query.
"WHERE table1.recordID Like "*" Forms![InputForm]!cmbRecordID "*" "
May be that's what you are looking for.
NeoPa 32,556
Expert Mod 16PB @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!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Paul MG |
last post by:
Hi
Template partial specialization always seems like a fairly
straightforward concept - until I try to do it :).
I am trying to implement the input sequence type (from Stroustrup
section...
|
by: Erik Wikström |
last post by:
In school (no I will not ask you to do my schoolwork for me) we talked
about policy-based design and got an assignment where we got the a code-
fragment from a stack-implementation. The idea with...
|
by: Christian Staffe |
last post by:
Hi,
I would like to check for a partial match between an input string and a
regular expression using the Regex class in .NET. By partial match, I mean
that the input string could not yet be...
|
by: Liu, Mingyi |
last post by:
Sorry if this question has been asked before. I tried to search in postgres mailing lists at http://archives.postgresql.org/pgsql-general/ just now and it gave me error "An error occured! Can not...
|
by: wecka |
last post by:
Hello All,
Does any one know how to use the form filter in Access to provide partial match?
When you choose to "form filter," all text boxes will convert to combo boxes where you can choose from...
|
by: SQL Learner |
last post by:
Hi Alex (Kuznetsov) and All,
This is to follow up with my last post, "Link two tables using partial
word match".
How can I UPDATE table using partial word match?
How can I write a SQL statement...
|
by: chungiemo |
last post by:
Hi
I have a project with access
it has 2 non-related tables in access, which should have been related by ID, due to the poor data entry standards I am trying to match records from table 1 to...
|
by: chungiemo |
last post by:
Hi
thought I would do another thread as this one is a bit different from the previous problem
I am looking for a solution to the relating problem
Comparing 2 access databases with 2 tables,...
|
by: tbucha3 |
last post by:
I have two tables that I have to get information from to produce another table. Both tables come from separate resources, one has partial accounts with addresses and the other has complete account...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |