LEFT JOIN on a partial match

[BWiddoes]

So I have read at least a dozen forums on this and all seem to have a solution but it is not working for me... Scenario, I have a table with Part No. and their correlating descriptions. I have a second table with a Product Number and its assosciated Part No. I want to view all of my Part No. with descriptions and IF there is a match to the Part No on the second table, I want to see the associated Product Number. Below is the layout and SQL on my test tables.

Test_Items1 (My Table)
Part No = 1317667223606 Desc1 = ABC123
Part No = 1317667223614 Desc1 = ABC456

Test_Products1 (Customer Data)
Product Number = 14_01 Referenced Item = 1317667223606 1317667223735 1317667224517 1317667225406

SQL (Which isn't working for me)
SELECT Test_Items1.[Part No], Test_Items1.[Item SX Descrip1], Test_Products1.[Product Number]
FROM Test_Items1 LEFT JOIN Test_Products1 ON Test_Items1.[Part No] LIKE "*" & Test_Products1.[Referenced Item] & "*";

The Desired Reults from my above scenario would be this
Part No = 1317667223606 Desc1 = ABC123 Product Number = 14_01
Part No = 1317667223614 Desc1 = ABC456 Product Number = Null/Blank

:banghead: The Part No from Test_Items1 may not always be the first string from the Test_Products1.Referenced Item. It may appear anywhere withing that group of Part Nos. The customer has them seperated by spaces. Maybe that is the problem? IDK Please help!

 

[plog]

The format of your data is confusing. Instead of equal signs, display it with column headers and then data delmited by commas. Using that format, does your data look like this:

[Product Number], [Referenced Item]
14_01, 1317667223606 1317667223735 1317667224517 1317667225406
14_03, 2217667223606 4117667223735 1317667224681 1317432254061

If so, you need to normalize it so that each discrete piece of data is in its own field like so:

[Product Number], [Referenced Item]
14_01, 1317667223606
14_01, 1317667223735
14_01, 1317667224517
...

Get it in that format and then your LEFT JOIN issue becomes easy. As long as every [Referenced Item] is delimited by spaces you can use VBA to move it into a new table with the proper structure.

 

[BWiddoes]

Hi Plog, let me know if this makes sense

Test_Items1
Part No, Item SX Desc1
1317667223606,Tape Dispenser
1317667223614,Clear A/S Tape

Test_Products 1
Product Number, Referenced Item
14_01, 1317667223606 1317667223735 1317667224517 1317667225406

Desired Results
Part No, Item SX Desc1,Product Number
1317667223606,Tape Dispenser,14_01
1317667223614,Clear A/S Tape,

This is the SQL I have which is not returning desired results.
SELECT Test_Items1.[Part No], Test_Items1.[Item SX Descrip1], Test_Products1.[Product Number]
FROM Test_Items1 LEFT JOIN Test_Products1 ON Test_Items1.[Part No] LIKE "*" & Test_Products1.[Referenced Item] & "*";

Clear as mud?

 

[Brianwarnock]

Read plog's post again and study his design for the test_products table.

Brian

 

[BWiddoes]

Oh right. Yeah, I see how that would work. Problem is, I'm not sure how to use VBA to do that. Any good recommendations, ie links, forums, search phrases?

 

[jdraw]

I don't think it's so much a vba issue as it is a normalization - define.design your tables issue.

Can you zip and post a jpg of your tables and relationships?

 

[BWiddoes]

Hopefully this is what you are looking for. All field Data Types are Text. I need to see every row from the Test_Items1 table and where the Test_Items1.[Part No] is in the Test_Products1.[Referenced Item], I want to see the associated Test_Products1.[Product Number] in my results.

 

[BWiddoes]

Problem is that I am using customer data and asking them to formalize it is not an option. I don't want to manually manipulate their spreadsheets so I thought bringing the data into access and performing a partial join would do the trick.. I am missing something though. The overall data batches I get have many more columns and records but if I can get the LEFT JOIN to work on a partial match, I can figure out the rest.

 

[jdraw]

I was actually requesting a copy of the database with no confidential info, and only enough records in the tables to test/adjust/work with the tables and the query.

As has been stated your table structures need to be adjusted to remove the repeating field.

 

[BWiddoes]

ha, I just realized that.