link child field with master field using comma as a seperator

[allentribe01]

I have two tables in my database, one named Products one named consumables.
The products table lists the sales price of products, the consumables lists
sales price and products it is compatable with. Some of the items in the
consumables list match multipal items in the products list (Consumables are
combatable with more than one product). I have created a from that lists each
product and its sales price. i have also added a subform that show the
consumables that are used in the product. I have linked the child field and
master fields to match the product code, but the field that has the model
comapatable with has multipaul values eg product1, product2, product3 etc.
The subform is displaying the consuables in the subform matching the product
codes, but the consumables that fit multipaul product it is not listing them.
I am assuming that the comma and the multipaul products listed in the field
is the issue, but how can i list these consumables in the subform for each
product it suits?

 

[Klatuu]

I am assuming that the comma and the multipaul products listed in the field
is the issue,

In deed it is. This indicates a serious flaw in your data design. You
should not be carrying multiple values in the same field. There is really no
way to do what you are wanting to do with your current table design.

It sounds like you need an additional table to resolve what is a many to
many relationship.

 

[allentribe01]

Thank you for your feed back. The question is how then do i set up a table so
i can list multipaul products that use the same consumable. I need the form
to list each prodduct seprately. with each consumable listed in the subform
below the product pricing.There could be up to 10 different consumables
suitable for each of the products.

 

[John W. Vinson]

On Tue, 11 Aug 2009 14:48:01 -0700, allentribe01

Exactly what Dave said.

You need THREE tables:

Products
ProductID
ProductName
<other info about the product itself>

Consumables
ConsumableID
ConsumableName
<other info about the consumable>

ConsumablesUsed
UsedID <primary key autonumber>
ConsumableID < what is this consumable>
ProductID <what is it used in>
<other info about THIS item in THIS product>

Rather than multiple values in one field, you would have multiple *records* in
a table.