I am a newbe trying to create an apartment management database. (Just a small thing to start out with)
I have tables for Buildings, Units, Tenants and finally Occupancies. The Occupancy table allows me to bring in all of the parts for the many to many relationships.
The problem is when I try to pull it all together in the layout for the occupancy.
First I choose the Tenant, then the Building... using value lists for each that combine the fk_TenantID and name and something similar for fk_BuildingID and Building Name... so far so good.
Now for the hard part... I can choose the Unit the same way... but I get a list of all the Unit IDs and Names. I know this sounds like what I am after but when it comes to Units many buildings have units A-Z etc. What I would like to do is have it also look at the Building ID which was previously entered and only supply the units for that building... or even better... only the available units for the building using some type of flag that the unit is unoccupied.
Any ideas would be greatly appreciated...Thanks