Reverse Relationship Lesson - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Reverse Relationship Lesson

David Pierce

5 years ago

Hi all,
I would love to see someone tackle the lesson on where you create a reverse relationship between two people or companies in my case. It works wonderful but is missing a key componant which i have yet to figure out how to do. It inserts two records into table and the question is:
How does one delete a relationship with a button? I Use this for partnerships between companies that have such a relationship like "Required Subcontractor" as the type of relationship. But if no longer or is desovled, i want to be able to delete that partnership and have both records removed from the table. Anyone want to give this a shot?
Thanks
Dave

	Richard Rost @Reply 5 years ago
Sounds like you're just deleting a record in a junction table. See today's TechHelp which is being posted shortly about many to many relationships.

	David Pierce OP @Reply 5 years ago
When the reverse relationship is created, it creates two records in the table. What i am seeking is a way to pull the relationship up and be able to delete both records in that relationship. Example, one record has Mom - Daughter and the reverse is Daughter - Mom so both records created need to be deleted.

	Richard Rost @Reply 5 years ago
I assume you have your table setup something like this: `RelationshipID, PersonID1, PersonID2` In that case you could do something like this: `DELETE FROM RelationshipT WHERE (PersonID1=X AND PersonID2=Y) OR (PersonID1=Y AND PersonID2=X)` That should delete both relationship records.

	David Pierce OP @Reply 5 years ago
I will work on that, sure will be nice! thanks

David Pierce OP @Reply

5 years ago

Good afternoon,
Its day two on watching videos, which i am doing one lesson set a day. So, i watched lesson two videos today and this reverts back to a different issue with the relationships. So, if you make a relationship then you make the oposit, thats great but what if you resolve that relation? You delete one but it doesnt delete the oposit. I want to if i delete a relationship, it delete both records or Oposit relationship also.

	Richard Rost @Reply 5 years ago
You're going to want to create your own button for that. Run the SQL I gave you earlier which should delete them both. You can't just use an OnDelete event.

	David Pierce OP @Reply 5 years ago
Thanks, I will have to play with that in my spare time. I am sure I will get it figured out as will be a good challenge!

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

Next Unseen

New Feature: Comment Live View