Table Question (1 Viewer)

tmyers

Well-known member
Local time
Today, 13:16
Joined
Sep 8, 2020
Messages
1,090
I am setting up a second database for another group of people here at my work place and I have a question regarding tables since I want to do this correctly the first time.

This app is for quoting apartment complexes to try and give an idea of the context. Here is a snap shot of it so far:
Capture.PNG

My question is on how to correctly setup the relationship between buildings and units. I am almost positive I need another table acting as a many to many between them. A single building can have multiple units, and a single unit can exist in different buildings.

Many to many tables still through me for a loop, so would anyone be able to offer insight in how I should go about setting it up?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:16
Joined
Feb 28, 2001
Messages
26,999
A single building can have multiple units, and a single unit can exist in different buildings.

If this is true then indeed you have found a situation requiring a junction table (many-to-many).

To set up a junction table, create it with not less than two fields that will be FOREIGN KEYS; one of them matched up to the PRIME KEY of BUILDINGS and the other to the PRIME KEY of UNITS. You would have one-to-many relationships with the "many" side on the FKs in the junction table and the "one" side on the respective PKs. For those cases where you have a unit split between buildings, you will have (at least) two records in the junction table, both with the same unit number but different building numbers. But where you DON'T have a split unit you still must have ONE entry in the junction. The side effect of this situation requires that you can no longer look at the BUILDING or UNIT table for location information EVER, because sometimes you have two answers. Even if for most cases you only have ONE answer, Access requires you to always look in the same place for BUILDING/UNIT relationships. If you HAVE a junction table, THAT is where you must always look.

I said "not less than two" fields because it CAN occur that there would be something to be stored that is neither specific to the building nor specific to the unit. As a contrived example of such "extra" data, suppose that when you have a split unit, you need two different keys and thus need to record two different key numbers. And as part of the contrivance, the keys are different from each other AND not necessarily related to the building they are in. Thus you would need a place to store key IDs that neither match up to the building nor match up to the single unit. That would be an example of extra fields in the junction.

As to how you deal with them on forms, they can be a pain - but the good news in your case is that if you DON'T have a split unit, you just make one entry with the BUILDING ID and the UNIT ID as the two FKs and you are done. So maybe if you have a split, you would have a check-box on a form that says "SPLIT UNIT" and if you check it, a second control opens up to allow you to define that second element of the UNIT. But you might prefer some other way to control this and, I have to say it this way, the GUI is up to you since there are so many ways you COULD want to do this.

In reports, MOST of the time you would either group by BUILDINGS or group by UNITS. So your query would be based on a JOIN between the junction and either BUILDINGS (if grouping by buildings) or UNITS (if grouping by units). This can be an INNER JOIN because from the description, there will never be a case of a "dangling" UNIT or a "dangling" BUILDING. You use LEFT/RIGHT JOINS only when you can have "danglers." In the detail section of your reports, there will be TWO detail records for split cases and one for non-splits.

You can always post more questions, of course. Hope this gives you an idea of what a junction looks like.
 

tmyers

Well-known member
Local time
Today, 13:16
Joined
Sep 8, 2020
Messages
1,090
I believe I understood it.
I made a table between the two and made it consist of:
BuildingUnitID (PK autonumber)
BuildingID (FK linked to Buildings)
UnitID (FK linked to Units)
Would I put quantity in this table? Or would I leave the quantity of the units in the unit table? Thinking somewhat logically, I would assume quantity would go in the new junction table. That way you see that Building F has 10 of Unit A.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:16
Joined
Feb 28, 2001
Messages
26,999
Post #3 names a nuance not covered by your discussion and partially misleading. If you are talking about unit TYPES being split across buildings, but no SINGLE UNIT ever splits across buildings, then you do not have a JUNCTION case.

If you have 10 units of type A in a building, you have 10 individual records with a type-code of A and the same building ID, and you can have a unit-type table with a code of A as the PK, with details of the TYPE of unit. The type-code would stay with the unit.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:16
Joined
Feb 19, 2002
Messages
42,971
This is a topic I am somewhat familiar with but I'm not going to get into this too deeply. You are taking advice from other experts and having several of us disagreeing on something complicated like this is simply too confusing for you. But, you really do have a m-m relationship between buildings and unit types. I think that UnitPanels is related to a specific unit number rather than to a class of units. If that is the case, then you need to put back the junction table which is the record for a specific unit and should actually include the apartment number.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:16
Joined
Feb 28, 2001
Messages
26,999
I may have asked my question unclearly, but Pat raises the same question I have asked. If the units must be treated individually, you need the junction table. If the units can be treated as a group (and are NEVER EVER treated individually), then and ONLY then do you have a case to skip the junction tables. There cannot be even a SINGLE CASE where you need to treat the units separately in order to avoid use of a junction table.
 

plog

Banishment Pending
Local time
Today, 12:16
Joined
May 11, 2011
Messages
11,611
We actually don't disagree (well I guess we meta-disagree: we disagree about disagreeing).

I think that UnitPanels is related to a specific unit number rather than to a class of units. If that is the case, then you need to put back the junction table which is the record for a specific unit and should actually include the apartment number.

I specifically asked how/why he needed to track Units and he said in general, not in specific. He doesn't care which unit has what, just that a building has so many of a certain Unit type. Seemed odd to me, but he explained it on the prior thread.

That's why I said he didn't need the junction table--because Panels weren't assigned to a specific unit. I agree he needs that junction table otherwise for sure--and I wouldn't declare a fatwah if he uses one anyway.
 

Users who are viewing this thread

Top Bottom