Does Access support JOIN ON LIKE SQL statements

[teambond]

Hi,

I am trying to join together a number of tables based on part number, where one of the tables the part number is embedded in a string in the field. ie PartNo RLT551A joined to 1234_RLT551a.

I have googled and found the concept of a SELECT * FROM table a JOIN Table b on b.column LIKE '%' + a.column + '%'. I know this nomenclature is not what's used for ACCESS so I tried a few variants but can't get it to work. Is it available for access?

If not, does anyone have any ideas how I can do this? I have tried doing it on cascading queries, but that doesn't work for me as I need the resultant records to be updatable and I think because it's a query based on a query it doesn't know the table details for the fields to be updated.

My only other alternative is to add the partNo (ie RLT551) to the orderpartno (1234_RLT551a) table, which would be very bad database practice.

Any help would be great.

Thanks

 

[MarkK]

One other alternative is to create proper Long Integer primary and foreign keys, and write code to find the matched records and link them properly. You can iterate through records in a recordset and use the InStr() to find your substring, and if you have a match, update the Long Integer foreign key to match the Primary key of the related record.

This will add value to your data. Tidy it up enough that you can enforce referential integrity, and then your data will stay tidy.

 

[teambond]

Thanks for your suggestion, MarkK. I'm not 100% sure I understand what you mean.

While I was waiting for a response, I tried setting up an extra field in the table that holds the composite part number that shows the ID field of the Orderdetails table. This is the left side of the composite part number (ie OrderDetailID 1 and PartNumber RLT551 make the orderedPart number 1_RLT551). I have made the ID field the pk and linked it to the Orderdetails table ID.


At the time of creating a record in the orderdetails table, I have a recordset loop that creates a line in the orderedpartnumber table at the same time. It thens links without needing any code and still allows updates.

I think I've broken data integrity rules by doing that though?

Can you please expand on what you meant?

Thanks

 

[spikepl]

The data integrity rules were broken at an earlier point, namely when the string abomination OrderNo_PartNo was concocted and used internally for data.

You have now arrived at the correct solution, the only thing remaining is to throw out the OrderN_partNo concoction from your table. Keep OrderNo and PartNo separate, as they should be. If you wish to present your string for the user, you still can: in a calculated field in a query.