Stumped on Value List
Hi All, I hope I can describe this properly.
I'm working on a CD database for classical music. I have a Product table (of CD's) a CompositionProduct (many) table a Composer table and a Composition table. For some (most) recordings I attach a Composer in CompositionProduct table. However sometimes I want to attach a Composition as well.
- I'm populating data using a CompositionProduct layout.
- The HMID is a drop-down value list of Product:HMID and displaying title too.
- The Composer ID is a drop-down value list of Composer:ComposerID and displaying ComposerName as well.
Here's what I'm trying to figure out.
- I want the CompositionID drop-down value list to only show me Compositions by the composer entered in the CompositionProduct:ComposerID field. In SQL terms, I'd like my value list to look like SELECT * WHERE CompositionProduct:ComposerID = Composition:ComposerID.
I've tried various relations from Composer:ComposerID -> Composition:ComposerID, to self-relations and never get the results I want. I can pretty easily get All compositions. Or just compositions that have been attached to in other CompositionProduct records. But I can't get just the Compositions composed by the ComposerID selected earlier.
I'm pretty sure this would be easy if I always had a Composition attached to CompositionProduct. Then I could reach through to Composer. But if I don't have any Compositions, and just want a Composer, I seem to be stumped.
Thanks in advance for any ideas. Scratching head.