Joining on "Hard Return" Values - Possible?

[ErikRP]

I need to join two tables, TableA and TableB.

These were originally Excel spreadsheets which are being converted to Access for some data massaging, and then going back to Excel.

TableA has fields:
Stock_Item, Price, Description, etc.

With sample data:
Q00001, 10.00, Blue Ball
Q00002, 15.00, Banjo
Q00003, 25.00, Ceiling Fan

TableB has fields:
Salesperson, Sale_Date, Stock_Item, etc.

With sample data:
Bob, Jan 15 2009, Q00001 Q00002
Jim, Jan 15 2009, Q00002
Mary, Jan 16 2009, Q00001 Q00002 Q00003


GOAL (via Query):
Bob, Jan 15 2009, Blue Ball Banjo
Jim, Jan 15 2009, Banjo
Mary, Jan 16 2009, Blue Ball Banjo Ceiling Fan


What I want to do is join the two tables on Stock_Item to get the Description from TableA into TableB. The join that I'm wanting to do would normally be quite simple, except that in TableB, Stock_Item has multiple values (entered via "hard return"). For those records where there is only one value, it's no problem to join. But where there are multiple values, I haven't figured out a way to make the join.

I know what would be the "ideal" would be to break out the multiple Stock_Items from TableB into separate records, however I need to have these in a single row when I export back into Excel.

Is there any solution? If not I will be left to do a find and replace on several hundred records.

Thanks!

 

[pbaldy]

To my knowledge, you won't be able to join those tables together. As you've noted, the best way to handle them in Access is with the data properly normalized, ie a record for each. It is relatively simple to concatenate them back together for export. Failing that, you're looking at a custom function that will step through the items listed and find the associated description.