many to many relationships - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

many to many relationships

Michael Poblet

5 years ago

I've created two forms each with subforms which link to a many-to-many relationship table.  Subject to complaint (multiple subjects can have multiple complaints and vice versa).

Now the boss wants another form to show which agency we shared complaint information with.  In this form he wants to be able to select the complaint and click on a button to take him to the complaint forms which will show details including associated subjects.

So this would be a many-to-many-to-many relationship.  Subjects to Complaints to Shared.  Not all complaints would be shared.

I've taken all the basic through expert classes but none of the developer ones yet.

	Adam Schwanz @Reply 5 years ago
Isn't this really just two many to many relationships? subject wouldn't apply to shared. Can you just make another junction table for complaints to shared then and subform it into the complaints?

	Richard Rost @Reply 5 years ago
Many to Many relationship between subject and complaint. If you share a complaint with multiple agencies, then that's also a M2M relationship.

Michael Poblet OP @Reply

5 years ago

Ok, so I would create another junction table (complaint x shared) create the subforms for each as normal. Now I will have two junction tables with complaints (complaint x subject) and (Complaint x Shared). What do I need to do to be able to cross reference details from the complaint? The boss wants to be able to click a button from the shared info form (which would have a complaint subform) and take him to the complaint form, which would have subject information from the (complaint x subject) junction table.

	Richard Rost @Reply 5 years ago
You can make a button on the complaint subform to open up the full complaint single form. See this video.

	Adam Schwanz @Reply 5 years ago
You should have a complaintID whether you're going from Subject all the way to Shared, or the other way around. So if you're in the area with the shared information and you say something using the where condition like DoCmd.OpenForm "ComplaintForm",,,"ComplaintID="&ComplaintID You're going to open the complaint form which will be able to access the subject information.

	Adam Schwanz @Reply 5 years ago
Ah yea if I misunderstood what you were asking, you can easily open up the full size form from the subform, either in a button or I like to do double clicking the ID field, in this case a quote number

	Adam Schwanz @Reply 5 years ago

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

Next Unseen

New Feature: Comment Live View