Want a list from Xtab

[AndreT]

I have a list of itemAs that has an attribute of multi-list of itemBs. I want to reverse the relationship to created a list of ItemB that has an attribute of multi-list of ItemAs.

Using shirt as an example, here's the original list:
Shirt 1 (yellow, orange, blue)
Shirt 2 (green, yellow, blue)
Shirt 3 (yellow, blue)
Shirt 4 (orange)

I want to reverse the relationship as:
yellow (shirt 1, shirt 2, shirt 3)
orange (shirt 1, shirt 4)
blue (shirt 1, shirt 2, shirt 3)


green (shirt 2)

Currently I have a function to convert the original list to a many-to-many relationship (associate) table, create a crosstab query, export to Excel then manually create the reversed relationship.

I can write a function creating another table to create the reversed relationship programmatically, but wonder if there's any easier way to do it Your advices are highly appreciated.

I'm currently using both 2000 and 2007 versions, and need this function in both environments.

[June7]

Is this list only shirts - how many types?

Does your function get the data into this arrangement:

ShirtID Color
1 yellow
1 orange
1 blue
2 green
2 yellow
2 blue
3 yellow
3 blue
4 orange

And the crosstab is:
Color Shirt1 Shirt2 Shirt3 Shirt4
blue 1 1 1
green _ 1 _ _
orange 1 _ _ _
yellow 1 1 1

If so, maybe this:
SELECT Color, IIf(Not IsNull(Shirt1),"Shirt1 ") & IIf(Not IsNull(Shirt2),"Shirt2 ") & IIf(Not IsNull(Shirt3),"Shirt3 ") & IIf(Not IsNull(Shirt4),"Shirt4") AS Shirts
FROM Table1_Crosstab;

If not:
http://www.blueclaw-db.com/concatenate_multiple_records_one_field.htm

[AndreT]

Hi, June7 -

Thanks for your response. That was a close one, but I don't know how many shirts I have, and there are always new shirts (figuratively speaking). I can do it in VBA opening the query and use field.name instead of hard code, but that would require VBA work similar to the link you provided. I've always had trouble converting xtab columns to a list, and was hoping there is an elegant way to do it using just query without using VBA stepping thru the records.

Thanks for your advice. I'll have to resort to programming for that.