I have enclosed a snap shot of my Location and relating table into this message.
My question is:
For most tenant that I have this layout works perfectly. I end up getting a problem for 4 tenant (at the moment).
I have setup 4 buildings(the whole building) itself as a tenant in order to be able to use the table and forms and reports for the 1st floor 2nd floor 3rd floor and so on(common area, rec rooms, mechanical room and so forth)
in order to keep my list short I need to create another table that would be called Sub-sub locations because each building have pretty much all the same components but none have them on the same floor in each building. I do not want to add another field into the rest of the forms and report queries because the bulk of everything is about the tenant who lives and rent units. the (building) account for very little and in the long run I would end up with thousand of blank field because this sub-sub location would not apply to anyone else but the building.
do you ahve any idea how to create another table that can be used only when needed?(if I need to enter something for the building itself)
This is how it might look on paper:
For tenants:
LocID = Units
LocSubListID= (Bathroom, kitchen, patio...)
LocItemID = (Toilet, sink, fans, lights ....)
For buildings:
LocID = (A Building, B building, C BUilding...)
LocSubListID= (1st floor, 2nd floor, 3rd floor.....
___________
Sub-sub= (Rec Room, Common area, hallways...)
____________
LocItemID = (Toilet, sink, fans, lights ....)