Multiple Many-to-Many relationships?

[dzeglen]

Hello,

I am relatively new to Access, and I'm still working my way through understanding relationships. I was hoping someone could provide me with some advice. I work for an NGO, and I'm trying to build a database for all our contacts who come to our events. I've attached an image of the relationships I have set up so far. As you can see, I've created a junction table so that there is a many-to-many relationship between the contacts and the events we've hosted. However, for our contacts' information, many of them belong to more than one organization. I thought I could make another many-to-many, but I can't make a PK with the OrgID in the contacts table. How do I solve this problem so that I can have contacts belong to more than one organization, and each organization have more than one member?

Thanks in advance.

 

[VilaRestal]

I think what you want is
rename OrgT as OrgContactsT
rename its OrgID field as ContactID and join that to the ContactsT.contactID

That gives you a many to many between contacts and organisations.

Presumably then you don;t need an OrgID field in Contacts. If that field's already got data in it (as an existing one to many between orgs and contacts) then you could

INSERT INTO OrgContactsT (ContactID, OrganisationID) SELECT contactID, OrgID FROM ContactsT

If on the other hand it means a special organisation (Primary perhaps) for the contact then it could be left but I'd rename to make it clearer what it stands for.

I expect that it could be removed though and the same goes for the Organisation field (duplicating data and effort)

 

[dzeglen]

Hi,

Thanks for the prompt reply! I did what you suggested, which makes sense to me.
However, when I open the contactsT page, and click on the plus sign beside the contactID, instead of seeing the events they've been a part of, it now asks me to "insert subdatasheet." Where do I go from here? If I insert the ContactsOrganizationsT table, I can no longer see the event data?

What should I do for this?

 

[VilaRestal]

Well you can only show one subdatasheet in a datasheet. You need to choose which you want, Events or Organisations.

In table design, properties window there is Subdatasheet Name property where you can specify which you want for a table.

But anyway, the way to display these two sets of records for each contact is in subforms to a form, which is how data should be being entered/viewed/edited anyway.

 

[dzeglen]

Ah. Understood. I see how it works now. Very helpful. I haven't gotten to form design yet, but I understand that that is where I should be inputting data when I add new records.

Thank you very much for your help, it is much appreciated!