Multiple realtionships to same table

[slrphd]

I have a question on multiple relationships and my search did not turn up anything connected to my issue. I have two tables, tblPlants and tblOwners. In this case the plants are nuclear power plants: big, complex, and very expensive. As a result, each plant has several owners. Worse yet, many Owners have an interest in more than one Plant. Since the maximum number of Owners per plant is seldom more than four, I have four Owner(1-4) fields in tblPlant. Initially, I wanted to connect each Owner field to tblOwner through a one(Owner)-to-many(Plant) relationship but I am having a hard time creating these relationships let alone enforcing referential integrity. What am I missing? I expect this is quite basic but, evidently, not for me.

I wish to sneak one other question on a different topic if it does not violate protocol. That topic is subforms. The books tell me how to build them. What I do not understand is what advantage do I obtain and when should one be used? Obviously, I have never done one and this issue seems to lay in the gray area between the textbooks and the developers manuals. Any insights will be greatly appreciated.

Thanks.
Steven L. Ross

 

[joeselch]

You should not be storing the plant owners in tblPlants.

Create a junction table, tblPlantOwners, to link plants with owners.
Your junction table should look something like this:

• fldPlantOwnerID (the primary key for this table)
• fldPlantID (the primary key from tblPlants)
• fldOwnerID (the primary key from tblOwners)

For data entry of Plant information, you should use two forms linked via fldPlantID:

• Main form - based on query of tblPlants
• Sub-form - based on the junction table, tblPlantOwners

You might make the sub-form a continuous form with a combo box for selection the plant owners.

 

[slrphd]

Thanks for the advice. I am beginning to undestand this a bit better thanks to your comments but I am not sure what a junction table is. I expect I can figure that out. I how I can reserve the right to ask further questions.
Steven

 

[joeselch]

I left out an important bit in my first post - establishing the relationships between the tables and enforcing referential integrity.

Once you have created your junction table (tblPlantOwners), open the Relationships window and join your tables:

• tblPlantOwners to tblPlants - join on fldPlantID, enforcing referential integrity
• tblPlantOwners to tblOwners - join on fldOwnerID, enforcing referential integrity

Additionally, you should probably define a multi-field index in the junction table to ensure only unique combinations of fldPlantID and fldOwnerID (so that the same owner can't be assigned to the same plant multiple times).

 

[slrphd]

Thanks for the additional advice. I shall have to spend some time sorting out everything but you have given me a good starting place and direction. Thanks.
Steven