Indirect Many to Many Self Join

[Steve]

I am trying to design my tables to keep track of relationships over periods
of a leases. The same people may lease from me on multiple occasions and
have different family members each time. I need to know who their family
members were at a given point in time so I can report it. I got some great
advice:


Another - more flexible, but harder to use in some ways - method is to

have an "Indirect Many to Many Self Join" - a fancy phrase which has a

fairly simple table structure. You'ld have your People table with

*all* people - the primary tenant and any family members; and a

Relationships table. This might have five fields:

Relationships

PrimaryPersonID <link to People table>

RelatedPersonID <also a link to People table>

Relationship <e.g. Spouse, whatever euphamism you're using for

'person of opposite sex sharing living quarters', Child, Mother,

Roommate, ...>

DateStarted <date this relationship started>

DateTerminated <date the lazy SOB finally left... oops! sorry! <g>>



Problem is I don't know how to setup the relationship. I have built the
tables but Access only lets me define one relationship from my People table
to my Relationship table. How would I actually relate the tables? I'm
missing something.

TIA

Steve

 

[John Vinson]

Problem is I don't know how to setup the relationship. I have built the
tables but Access only lets me define one relationship from my People table
to my Relationship table.

Ah yes. Should have mentioned that!

Add the People table to the relationship window *twice*. It's only one
table, but you'll see two icons. Join each instance of People to one
of the foreign keys in the Relationship table.

 

[Steve]

Thanks very much John!

I had no idea I could do this. Would it be correct then to assume I can add
a table to the relationship window as often as I'd like just to make things
easier to follow? Could I have the tblPeople on the upper left part of the
window related to several tables to the right and then also have tblPeople
in the lower left portion of the window related to several more tables to
the right?

 

[John Vinson]

Thanks very much John!

I had no idea I could do this. Would it be correct then to assume I can add
a table to the relationship window as often as I'd like just to make things
easier to follow? Could I have the tblPeople on the upper left part of the
window related to several tables to the right and then also have tblPeople
in the lower left portion of the window related to several more tables to
the right?

Yep. Access won't complain.