6 Replies Latest reply on Dec 9, 2015 4:37 AM by coherentkris

    List in a Complex Relationship

    director@charlesmoore.org

      This is from a database where a design firm can make design proposals for companies.  A proposal will always only have only one company.  A company can have many buildings.  But the proposal may only focus on, say, 2 out of 3 of its buildings.  (Those same buildings can have other companies as tenants.)  Rooms are assigned to buildings, as well as companies.

       

      I have attached screen shots, plus the actual database.

       

      So if for proposal-1000, the user chooses ACME as the company.  And then selects only the Brown Building and Red Building as subjects for the proposal.  (A value list only provides buildings assigned to ACME as candidates for the proposal.)

       

      How do I compile a list of rooms, rented only by ACME in the Brown Building and Red Building?  (Not the rooms in the Green Building, and not ones rented by Life Technologies or Sprockets.)

       

      The correct list should only have:

      room-1008

      room-1009

      room-1005

      room-1006

       

      THANKS SO MUCH FOR ANY HELP.