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:
Code:
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)