2 Replies Latest reply on Aug 22, 2012 4:33 AM by comment

    Creating a more efficient join in the relationships

    kart97

      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

       

      Relationships.png

        • 1. Re: Creating a more efficient join in the relationships
          comment

          Strictly speaking, an occupancy belongs to a unit, and unit only. The unit in turn belongs to a building - but having a BuildingID in the Occupancies table is redundant, since the building is already given by the core relationships:

           

          Buildings -< Units -< Occupancies >- Tenants

           

           

          However, since you want to select a building first, you will need to add an auxiliary relationship as:

           

          Occupancies::BuildingID = Units 2::BuildingID

           

          This will make all units of the selected building related. Use this relationship to define a value lists of units, showing only related records.

          • 2. Re: Creating a more efficient join in the relationships
            comment

            kart97 wrote:

             

            or even better... only the available units for the building using some type of flag that the unit is unoccupied.

             

            That may be more complicated, because - if I understand correctly - a unit is "occupied" by virtue of having a conflicting occupancy?