AnsweredAssumed Answered

Value List from Complex Relationship

Question asked by director@charlesmoore.org on Dec 8, 2015
Latest reply on Dec 9, 2015 by director@charlesmoore.org

I have tables for:

 

DESIGN SERVICES

Companies

JOIN Companies to Buildings

Buildings

Rooms

 

(Companies can have many Buildings; Buildings can have many Companies; Buildings can have many Rooms; a Room can belong to only one Company and Building.)

 

In the Room table I have have a field for the Company_pkID, so the user assigns rooms to Companies, from a Value lists generated by a relationship between companies and buildings.

 

When in a DESIGN SERVICES table my client chooses the Company (Only One) and Buildings (one or more)  from Value Lists (using radio buttons and check boxes), is there a way to create a list, either by relationships or calculations, that compiles all of the rooms in the selected Buildings, but LIMITED to the selected Company?  The snag seems to be in getting around the many to many relationship between companies and buildings.

 

Any hints?  Thanks!

Outcomes