Lookup value on multiple items in a comma separated field

[btappan]

I have a table like this below:

ProductID ProductName


1 AAA
2 BBB
3 CCC


I have a field in another table, like this:


RelatedProducts
AAA,CCC
AAA
AAA,BBB,CCC


I need a field in one of my queries that will return the related products field data like this:

RelatedProducts
1,3
1
1,2,3

Any help?

[btappan]

Ps. I would just do text to columns in excel, but some fields have up to 10 separate values while others only have 1 or 2, so it'd be ugly, plus as new info is added to the db, it'd have to be done again. Looking to do it programmatically.

[btappan]

no luv, anybody?

[rpeare]

Why are you using a multiple value field. The reasons for using them are very, very few and far between. As you've found out multiple value fields are a pain in the ass to deal with if you want to get information back out of them. What you really should have is a separate table that has a ONE TO MANY relationship FROM your parts to 'related parts'.

Additionally, your 'description' I am assuming is originally coming from your parts table, so what happens if the part description changes? even if you successfully parse the descriptions you are not necessarily going to be able to find the matching part number.

Any suggestion I'd give you would involve creating a table that would correctly normalize the data then re-concantentate it to get the format you want. So the base question is.... why are you using the multiple value field instead of correctly normalizing your data? if there's a good reason and that reason can't be changed it really affects how you'd approach this.

[June7]

Agree with rpeare. I have NEVER designed a db with MVF.

Dealing with them requires a query that expands the multiple values to individual records, essentially the result if they had been in separate normalized table you built instead of letting Access do it behind the scenes.

Do you have a Lookup built on the multi-value field? That's probably why you see the text. Remove the lookup and will probably see the IDs.

Lookups with alias in tables is something else I (and probably rpeare) recommend against.

If you are instead saving the actual text descriptor - well, rpeare has described issue with that.

[btappan]

I'm using a multiple value field because that is how the data comes from a third party. I want to avoid having to import the data all of the time and wanted to simply use a linked table to the mfr provided spreadsheet.

[June7]

Link to spreadsheet? So this is not technically a multi-value field - it is just a string of comma separated values?

For what you want, that may be worse. I am seeing VBA code and temp table in your future.

How many products are there?

[rpeare]

If he's importing it he can set up an import process that breaks up the MVF into a normalized table structure at the same time rather than having to contend with it after the fact don't you think June? It would be far easier, to me, to write the VB code on the front end import (and attach it to a button on the main form or something). If you are getting a text description from an external report and you want to relate that to your item numbering structure you're in trouble no matter what you do. If the item is mistyped, changed deleted (in either system) you will not find a match and create all kinds of other problems. But as June said you're going to need to use VBA code to break it up the very worst case is that you attempt to keep all those items in the same field. In terms of a 'quick fix' the best idea might be to import the data, then break up the field that has the descriptions into 'Related_Product1', 'Related_Product2', .... etc where you'd have to have a number of columns equal to the part with the highest number of associated parts. That would allow you to put it back together relatively easily it would just involve linking your products table into query where you want the internal product numbers the same number of times as the maximum number of related products.

[June7]

A link to a spreadsheet might be easier for less experienced than an Excel object in VBA. Either way, the code to convert the values from text to the IDs would be the same with the same issues. The main point is a query alone cannot get from A to Z in this situation.