Join query with partial match

S

Steve_A

I tried to search for this but I am not sure how to search it.

I have “Table1†with a PN field and I need to join that to the PN field in
“TableA†but it is buried in a string. Is this possible as it is not always
at the start or end of the string? I can go get them one at a time using
*148T5-400* but that will take a while doing several hundred


Table1 TableA
PN PN
148T06-905-400 MOD/148T06-905-400ER/R1
148T06-905-400 148T06-905-400ER/R2
148T06-905-400 DEVIATION/148T06-905-400
148T06-905-400 MOD/148T06-905-400
141A68-1 M0D/141A68-1
141A68-1 141A68-1PIA
141A68-1 REVA141A68-1PIA

TIA
 
K

KARL DEWEY

Try using a second query with two output fields and join it with the two
tables in a query.
Table1
PN
148T06-905-400 ---JOIN---- Query_1
Field_1 Field_2
148T06-905-400 MOD/148T06-905-400ER/R1
TableA -------------------------------JOIN-------
PN
MOD/148T06-905-400ER/R1
 
J

John Spencer (MVP)

If you don't need to edit the records then you should be able to use a
non-equi join in the query. A noon-equi join cannot be built using the query
design view (the grid)

SELECT TableA.PN, Table1.PN
FROM TableA INNER JOIN Table1
ON TableA.PN LIKE "*" & Table1.PN & "*"

You can build most of this query in design view.
-- Add both tables
-- Add the fields you want to see
-- Drag from PN to PN
-- Switch to SQL view (VIEW: SQL from menu)
-- Edit the string
ON TableA.PN =Table1.PN
to read
ON TableA.PN LIKE "*" & Table1.PN & "*"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
S

Steve_A

John, that looks like it did the trick, I just didnt think of going to SQL
view to use wildcards.

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top