More than one value for a foreign key

[Dan]

Hi all,
I have two tables which look something like this:

tblProd
prodID - Key
prodName
prodColorID - Foreign Key

tblColor
colorID - Key
color

In the tblProd for prodName I have a product called "Water Bottle". In the
tblColor I have several colors, each with it's own ID number. For example:
White = 1, Red = 2, Blue = 3, Yellow = 4, Gray = 5, Blue = 6, Black = 7.

Now, what I'm trying to do is to show that one product may have more than
one color. For instance: Water Bottle (prod. name) - Blue, Yellow, White. I
can get Water Bottle - White (any one color, but I want to show all three
colors). Plus, I'm trying to put this on an a small ASP learning site.

Any suggestions?
Thanks in advance,
Dan

 

[Jeff Boyce]

Dan

I believe you just described a "many-to-many" relationship, not, as you
currently have designed, a "one-to-many".

If your products (e.g., water bottle) can have one/more colors, and your
colors can be applied to one/more products, you have many-to-many.

You (and Access) resolve this with a third table. This table holds valid
pairs of ProductID and ColorID. From the example you gave, there would be 6
rows, all with the same ProductID, but each with a different ColorID.

Good luck

Jeff Boyce
<Access MVP>

 

[Dan]

Thank you Jeff for the tip. I tried it, but I don't think that it's doing
what I need it to do. What I'm now getting as a result is a query table with
something like this:

prodID prodName color
6 Water Bottle White
6 Water Bottle Yellow
6 Water Bottle Green

What I would rather like to see is something like this:

prodID prodName color
6 Water Bottle White, Yellow, Green

Thanks again,
Dan

 

[Jeff Boyce]

Dan

I suspect you'll have more luck with that kind of display if you use a
report, rather than a query. At any rate, I seem to recall a routine Duane
Hookom created to concatenate multiple related values.

"Generic function to concatenate child records..."

http://www.rogersaccesslibrary.com/OtherLibraries.asp

Good luck

Jeff Boyce
<Access MVP>

 

[Dan]

Jeff,

I'm most thankfull for your help with this. I took a quick look at this site
and it looks as if it's what I was looking for. I'll be traveling next week,
so I'll check it when I return and post if it answered my question or not.
Yet it seems as this to be the key.

Thanks a million,
Dan