Update table based on multiple rows of Select Query 1

[freebirdnal]

I am struggling with something I have locked myself into:

I have 2 tables with a one to many relationship. I need to update the single based on the multiple corresponding rows.

table1
itemNumber (PK)
colorsAvail

1
|
|
MANY

table2
itemNumber \
size | together make up PK
color /
qtyInStock

I use:

SELECT DISTINCT table2.itemNumber, table2.color
FROM table2
WHERE ((table2.qtyInStock)>0);

to get rows of individual colors in stock for each item. And I want to use these multiple rows on each item to create the table1.colorsAvailable field as a string of colors "red, green, blue"

I've been fiddling with ADO and VBA but my knowledge is limited- im sort of teaching myself as i go along. ive gotten nowhere.

Can anyone help me with some code to accomplish this? It's holding up my whole project (probably should have considered my abilities before i designed this) and im feeling pretty frustrated. thanks!!!

 

[dhookom]

Look at the generic concatenate function at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane
MS Access MVP
Find out how to get great answers faq219-2884.