I have tables for:
JOIN Companies to Buildings
(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!