How to Join tables on a partial string match in MS Access

[amad]

Hi Friends,

I have two huge tables "FirstTable" and "SecondTable" imported to access and I'd like to query between these two tables for records that have Part numbers (under a name "P/N") in both tables.




I have two issue her:
First: One table -FirstTable- contain just first 10 digit of Part Numbers and the other table -SecondTable- has full Part Numbers such as “6435J32457” and other “6435J32457p004”.
Second: Some Part numbers not available in both tables and other table -SecondTable- has more than 10,000 records without P/N, So when i join them i got a hug records that i don’t need.


I need query to (1)return all records where P/N is partial string match between two tables, (2)does not include all blank values in P/N for FirstTable.

I appreciate any assistance you can provide. Thanks

[Micron]

In the query criteria row of the field for the table that has the entire record put Like "*" & [fieldname] & "*" where fieldname is the name of the field where only part of the value is entered. You should not need to add AND <> NULL to that if the join between your tables is a inner join since NULL would not be LIKE any value in the table which has the partial number. I presume you know how to create a query and join the two tables.

[amad]

Thank you Micron, I create a query and i put the following:

WHERE(((FirstTable.P/N) Like" *" & [SecondTable].[P/N] & "*");

it Works, Thanks

what about the other issue for removing Null from my query and how i put it inside WHERE.

[Micron]

Try Is Not Null in the query criteria row for the field for [second table].[p/n]
I think it should end up as
WHERE(((FirstTable.P/N) Like" *" & [SecondTable].[P/N] & "*") AND [SecondTable].[P/N].Is Not Null