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

 

[jmrSudbury]

I think this is what you are looking for:

SELECT tblMainData.Location, tblMainData.P1, tblMainData.P2, tblMainData.P3, tblMainData.Status, tblGroupList.PN, tblGroupList.[Group Name]
FROM tblGroupList, tblMainData
WHERE (((tblMainData.P1) Like "*" & [tblGroupList].[PN] & "*")) OR (((tblMainData.P2) Like "*" & [tblGroupList].[PN] & "*")) OR (((tblMainData.P3) Like "*" & [tblGroupList].[PN] & "*"));

John M Reynolds