Complex Queries.

Hi,

I am trying to build a database to keep records of devices that I have.

The basic ERD is as so:
hXXp://img50.imageshack.us/img50/3082/erdkt3.gif
I want to create a form where the user selects the building, then is given a list of rooms in that building, the is given a list of Racks in that room.

I have tried added fields Bldg and Room in the device table. The bldg drop down would
SELECT Bldg_ID, Bldg_Name
FROM bldg;

Then in Room i did a:
SELECT DISTINCT [Room].[Room_ID], [Room].[Room_Name] FROM Room, Device
WHERE Device.Bldg_ID=Room.Bldg_ID;

Then in Rack I did:
SELECT DISTINCT [Rack].[Rack_ID], [Rack].[Rack#]
FROM Rack, device
WHERE Rack.Room_ID=Device.Room_ID;

This work except for one flaw. there where statement limits what room/rack for all bldg's or rooms in the table not just that current row.

I then started to think I need to do this in a form. But i run into the same problems. I hope this makes sense.

Send me a PM and I can send you a copy of the file.

Thanks,
Mitchell