Yes, a room can have no cabinet. A few have more than 1.
Which means CabinetID doesn't belong in Room table. You need a 1 to Many eg RoomContainsCabinet
You really don't need forms at the moment. You probably did that to populate tables. For a few test records, I'd stick them in via the table--but that's only for a few test records in each table. In fact, you can map out your test record on paper an enter them into the tables.
I went back to the original database I sent in zip and added some test records. Here are a couple of queries to get info. This is pretty basic, but is intended as proof of concept. I may have misunderstood various facts, so nothiing here is carved in stone.
Customer
CustomerID Organization ShopName OfficeSymbol Rank LastName FirstName PhoneNo Email
1 ABC alpha @##$# Doe John
2 ABC beta $$@ Payne Hezza
3 XYZ charlie (() Bear Kodiack
Building
BuildingID BuildingName
1 EAST TOWER
2 PhysScience
3 HeavyEquip 100
4 FireStation6
FacilityMgr
CustomerID BuildingID
1 2
2 1
POC
CustomerID BuildingID RoomID
3 4 3
Query - WhoAreFacilityManagers
LastName FirstName BuildingName RoomName
Doe John PhysScience TrainingRm1
Doe John PhysScience TrainingRm2
Doe John PhysScience TrainingRm3
Doe John PhysScience Exec Complex
Payne Hezza EAST TOWER BoardRoom
Payne Hezza EAST TOWER LunchArea
Query --POCForSpecificRoom
SELECT Customer.LastName, Customer.FirstName, Building.BuildingName, Room.RoomName
FROM (Building INNER JOIN Room ON Building.BuildingID = Room.BuildingID) INNER JOIN (Customer INNER JOIN POC ON Customer.CustomerID = POC.CustomerID) ON (Room.BuildingID = POC.BuildingID) AND (Room.RoomID = POC.RoomID);
Query --ShowAllPOCByBldgAndRoom
Code:
SELECT Customer.LastName
,Customer.FirstName
,Building.BuildingName
,Room.RoomName
,"SpecificRoomPOC" AS ROLE
FROM (
Building INNER JOIN Room ON Building.BuildingID = Room.BuildingID
)
INNER JOIN (
Customer INNER JOIN POC ON Customer.CustomerID = POC.CustomerID
) ON (Room.RoomID = POC.RoomID)
AND (Room.BuildingID = POC.BuildingID)
UNION
SELECT Customer.LastName
,Customer.FirstName
,Building.BuildingName
,Room.RoomName
,"BldgPOC" AS ROLE
FROM (
Building INNER JOIN Room ON Building.BuildingID = Room.BuildingID
)
INNER JOIN (
Customer INNER JOIN FacilityMgr ON Customer.CustomerID = FacilityMgr.CustomerID
) ON Building.BuildingID = FacilityMgr.BuildingID;
Which gives output
Code:
LastName FirstName BuildingName RoomName role
Bear Kodiack FireStation6 LunchRoom SpecificRoomPOC
Doe John PhysScience Exec Complex BldgPOC
Doe John PhysScience TrainingRm1 BldgPOC
Doe John PhysScience TrainingRm2 BldgPOC
Doe John PhysScience TrainingRm3 BldgPOC
Payne Hezza EAST TOWER BoardRoom BldgPOC
Payne Hezza EAST TOWER LunchArea BldgPOC
Windows XP 
Access 2003
Reply With Quote
Originally Posted by orange
