return list

[mmori]

I have a database of light fixture symbols/identifiers, i.e. E01, E02, etc. The table contains other information about the fixture. In this table I have created a string to store a list of locations.

I have another table with records that contain a room property as well as the light fixture key. So for example, a series of records could be:
E01 - living room
E02 - living room
E01 - dining room
E03 - kitchen
E01 - kitchen

The tables are linked and I want to create a report that shows all the light fixture data, including a list of all the rooms in which that fixture is used. I can't seem to quite get my head around a procedure that will do this.
Example:
E01 - living room, dining room, kitchen
E02 - dining room
E03 - kitchen



I created a crosstab query that shows the quantity of each light fixture in each room, but I can't figure out how to use that to get what I want.
Ideally, all I want is a string to use in a report, but I'm not sure how to get it.

I think I've just gotten too bogged down in the problem. If anyone can offer a suggestion on how to accomplish this, I would appreciate it.

[pbaldy]

This type of thing?

Return a concatenated list of sub-record values

http://allenbrowne.com/func-concat.html

[mmori]

I think that might just do the trick. Thanks.

[pbaldy]

No problem.