Is there a way to group items from multiple records into a single field?

[cherold]

I don't know if this is possible, but I would like to have a form that folds up information from multiple related entries into a single row.



I have several tables all designed to keep track of my classical record collection. One table is of classical compositions. The second is of physical discs (since a disc can have multiple compositions. The third holds composer names, and the last keeps track of what compositions are on what discs. (Tables at bottom of post.)

If I query all these together, I get something like:

DiscID	DiscTitle	Composition	Composer
1	Classical Hits	Pachelbel's Canon	Pachelbel
1	Classical Hits	Flight of the Bumblebee	Rimsky-Korsakov
1	Classical Hits	Air on a G String	Bach
2	Beethoven 1/9	Concerto No. 1	Beethoven
2	Beethoven 1/9	Concerto No. 9	Beethoven

What I would like is a list of discs with the compositions of a disk concatenated into a single field, like this:

DiscID	DiscTitle	Compositions	Composers
1	Classical Hits	Pachelbel's Canon, Flight of the Bumblebee, Air on a G String	Pachelbel, Rimsky-Korsakov, Bach
2	Beethoven 1/9	Concerto No. 1, Concerto No. 9	Beethoven

This may be impossible, but you never know. (If there's no way to do this in a form but it can be done in a report let me know. I never use reports so don't know how flexible/inflexible they are. I just use forms because they're familiar.)

Here are simplified versions of each table:

Discs:
DiskID 
Title 

Compositions:
CompositionID 
CompositionTitle
composer_id 
 
Artists:
ArtistsID (this connects with Compositions.composer_id)

DiscContents:
disc_contents_id 
composition_id (connects with Compositions.CompositionID)
 disc_id (connects with Discs.DiscID)

Classical Hits

[pbaldy]

A commonly used method:

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