AnsweredAssumed Answered

Stumped on Value List

Question asked by tomoy on Apr 26, 2013
Latest reply on Apr 30, 2013 by tomoy

Title

Stumped on Value List

Post

      

     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.
      
     Tom
      

Tables.png

Outcomes