Table Comparison / Partial Text Comparison??? (1 Viewer)

[dkinnz]

I’ll just state my question/problem right off the bat:
Is there a way (hopefully using query criteria, but VBA code would be fine also) to compare the values of one table with another table by looking for similar/partial text?

This problem is kind of complicated to explain, so please bear with me. I’ve attached my database b/c it’s easier to see what I’m talking to go along with my explanation.

I have a table (called tblGroupList) that has two columns. One of the columns in this table provides the values for a combo box in the user form. Each of these values corresponds to data in the other column of the table.
For example:
tblGroupList
Column 1 (PN) Column 2 (Group Name) – used in combo box
A1234 Alpha
A1235 Alpha
A1236 Alpha
A2255 Beta
A2244 Beta
A2254 Beta
Etc…

So, when the user selects Alpha from the combo box, it corresponds to “A1234”, “A1235” and “A1236”. A search is then done on three columns in the main data (tblMainData) to find all the records that contain any of these values.
The query criteria under “tblGroupList.PN” that does this is below:

 [tblMainData].[P1] Or [tblMainData].[P2] Or [tblMainData].[P3]

This works fine if the values between the two tables match, but the problem is that 90% of the time the main data that I’m trying to filter does not completely match what’s in my tblGroupList table.
For example, the main data records might contain “A1234-XX”, “A1235XX”, and/or “A1236XX-X”. So, I’d like to just recognize the partial text similarity to “A1234”, “A1235” and “A1236” from tblGroupList.
I’m guessing this can be done using a partial text search, but I’m not sure how to do this.

Phew. I hope that made sense. Like I said, this problem is easier to understand if someone would be kind enough to take a look at my database and offer any suggestions on how to solve this problem.
I really, really, really appreciate any help that can be given.

Regards,
dkinnz

 

[The_Doc_Man]

Look up "LIKE" as a comparison operator.

SELECT x, y, z, ... WHERE [A] LIKE "A1234*"

Gives all records where [A] is or begins with "A1234"

The only problem is combinatorial math. You are doing a Cartesian comparison and that can take a LONG time.

For instance, if you have 100 patterns to find and 10,000 records in which to find them, you will be doing your search 1,000,000 times. So unless your data set is very small, this search of yours might take a while.

What you MIGHT consider, if this is even possible, is to take your reference table, determine the length of the reference keys, and then look at your search-object table to make a query that JOINs the reference to the object table, with the literal value of the reference table but a LEFT$ function of the object table. In effect, joining the reference table to a specific number of characters from the selection-key field. Access can do this a LOT faster if you use JOIN techniques.