Many to Many Same Table - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Many to Many Same Table

Clay Boepple

2 years ago

I'm still going through the Expert Level videos, but was wondering how handle an example like this:

I have a customer table. I have 4 siblings that are each a customer. They each have given SOME of their siblings Power of Attorneys (POAs) to sign documents. On each customer's form I want to list who they have POA for. For instance:

John, Jane, Sarah, & David are customers. John has POA for all siblings. However, Jane only has POA for John & David.

There are other relationships between customers that I will need to make as well.

Thanks,

Richard Rost @Reply

2 years ago

Great question. The junction table stores information about the relationship between the two records, so I would store the POA in the junction table. Even if the junction table relates a person to another person.

PersonT: PersonID, FirstName

JunctionT: JunctionID, Person1ID, Person2ID, HasPOA



PersonT

1, John

2, Jane

3, Sarah

4, David



JunctionT

1, 1, 2, True (John, Jane, POA)

2, 1, 3, True (John, Sarah, POA)

3, 1, 4, True (John, David, POA)

4, 2, 1, True (Jane, John, POA)

5, 2, 3, False (Jane, Sarah, NO POA)

6, 2, 4, True (Jane, David, POA)

etc.

Make sure you watch my video on Self Join Relationships too.

	Sami Shamma @Reply 2 years ago
Richard , this a very elegant solution.

	Clay Boepple OP @Reply 2 years ago
Richard, thank you so much for help! Your training has helped so much and has allowed me to see the potential of using MS Access in our office.

	Richard Rost @Reply 2 years ago
Glad you like it.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View