Many-to-Many Relationships in Forms

[Fred Worthington]

Greetings:

I have two tables, one for Volunteers and one for Organizations. Each
Volunteer may serve multiple Organizations and each Organization may be
served by multiple Volunteers. I have created a many-to-many relationship
between these two tables by creating a Junction Table. Now I am stuck as to
how to update the records. It is my understanding that a subform can only
have a one-to-many relationship with the master form. Can anyone suggest
how I can set this up so my Volunteer Forms will indicate which
Organizations they are serving as well as having the Organization Forms
display all the Volunteers serving them? If I can't use subforms, what is
the best way to display the associations between Volunteers and
Organizations, as well as entering data into the records?

Thanks . . . Fred

 

[Allen Browne]

To enter organizations and choose which individuals serve in them, create a
main form bound to Organization, and a subform bound to the junction table.
If there are no more than a few thousand volunteers, you could use a combo
box in the subform where you choose the volunteers for the organizatsion.

To enter individuals, and show the organizations they serve in, create a
main form bound to the Volunteer table, and a subform bound to the junction
table. Again, you can use a combo to select the OrganizationID foreign key
in the subform (one per row).

 

[Fred Worthington]

Thanks for your response Allen.

There is still one thing regarding the Junction Table about which I am
unclear. Is it supposed to contain data (the list of Volunteers and
Organizations), or does it simply serve as a link? Also, are you suggesting
that the combo boxes be the only Fields in the subforms, or are the subforms
supposed to contain additional data from the Volunteers and Organizations
Tables (I'm not sure what you mean by "one per row")?

Thanks again . . . Fred

 

[Allen Browne]

The junction table will contain at least these fields:
VolunteerID - foreign key to Volunteer.VolunteerID
OrganizationID - foreign key to Organization.OrganizationID.
It may also contain other fields, such as the date this person started
volunteering for this organization, or the frequency of service.

If there are no other fields, then the subform might contain only the combo
box. If there are other fields, the combo will have several fields on the
row (datasheet or continuous form view).

 

[Fred Worthington]

Thanks again Allen,

I have the two foreign key fields you describe in my junction table. I also
have placed a subform (with the junction table as record source) containing
a combo box in the Volunteer Form that I want to provide a drop down list of
all the Organizations. However, my combo box is empty. I was wondering if
this was because the fields in the junction table are empty. I have assumed
the data would come from the other Tables (Volunteer and Organization), by
way of their relationship with the junction table. I think I am about to
get this to work if I just have a little better understanding of the data
flow.

Thank you very much for your help . . . Fred

 

[Allen Browne]

If you want the combo to show the fields from the Organizations table, set
its RowSource property to Organizations.

 

[Fred Worthington]

Thanks Allen,

As soon as I deleted the Relationships between my Organizations Table and
the junction table, the combo box worked perfectly, and I can probably go
ahead with it this way. However, my original objective was to create a
simple way for the data entry person to associate Organizations with each
Volunteer while simultaneously associating the Volunteer with each
Organization they serve without having to jump back-and-forth between Forms.
The problem I am having may be arising from two conditions. First, both the
Volunteer and Organizations tables already have several hundred records,
complete with addresses, phone numbers, and primary key ID's, etc. Second,
I wanted to establish a many-to-many relationship between the two tables
(since each volunteer can serve more than one organization and vice versa).
I thought I could achieve that objective by way of the "junction table."
Perhaps my original idea is not the best approach. The way it is now, as
each organization is assigned to a volunteer, the data entry person will
have to separately open each organization form and key in the volunteer's
name to associate them with that organization. I had hoped it could be set
up so they would automatically update each other to save the extra steps and
assure accuracy.

Thanks . . . Fred

 

[Allen Browne]

I think I'm missing something here, Fred.

The junction table is definately the way to go.

If you made no other changes, and the combo in the suform worked correctly
as soon as you deleted the relationships between the junction table and the
2 primary tables, then either you have a corrupted index, or else the bound
column of the combo is not the foreign key field that it should be.

You can have subforms in both your Organisations form, and in your
Volunteers form. Both subforms will be bound to the same junction table.
When you enter an individual into the Volunteers form, you can enter their
organisations into their subform. You can also view/enter information about
the volunteers for an organisation in the Organisations form. It works both
ways, because both are bound to the same junction table. If that is not
working correctly, perhaps the MasterLinkFields/ChildLinkFields of the
subforms are not correct.

 

[Fred Worthington]

Allen,

I have been working with the Volunteer Form using the VolunteerID as the
Master/Child Link (which appears to be set up correctly in the properties
window). However, after placing the Organizations subform in the Volunteer
form, and thinking all was well (because the Volunteer ID is synchronized in
both forms and the drop down list of Organizations appears correctly), I
have now discovered that when I try to select an Organization from the drop
down list in the combo box, nothing happens. So I've obviously got a
problem somewhere. Maybe this is a clue as to why my junction table is not
working. For now I'm going to troubleshoot my combo box and will report
back to you when I've made some progress.

Thanks . . . Fred