Many-to-many-to-many

[EarlCPhillips]

I am developing an application for an environmental group that has forms for
people info, event info and sponsor info, each with subforms for the other
two types of data. The people form has a many-to-many subform relationship
with events and with sponsors. The Events form has a many-to-many subform
relationship with people and with sponsors. The Sponsors form has a
many-to-many subform relationship with people and with events. There are
intermediary tables for the people-events, people-sponsors and sponsor-events
relationships.

There are combo boxes on each form showing the opposing two tables. Within
these combo boxes I have coded a Click event in VBA that creates the
intermediary table relationship between the form's table and the combo table
when an item is selected within the combo box. I can create the intermediary
tables just fine, but I am having trouble displaying within the subforms on
each of the forms all of the members in the relationship for the other two
tables, e.g. "the people form does not display all members of the
relationship to events and all members of the relationship to sponsors" with
similar situations on the other two forms.

Is there a way to display all members of a double join on an intermediary
table within a subform or must I code some special additional SQL to display
it?

On the people form they want to display all events the person is involved
with and all sponsors the person supports. On the event form they want to
display all the sponsors and all the people supporting them. On the sponsor
form they want to display all the events they are involved with and who helps
them.

Can anyone help me with how to code the two subforms to pick up all members
of the other two major tables? Any help would be appreciated.

Earl Phillips
Ex-mainframer trying to learn Access

 

[Tim Ferguson]

=?Utf-8?B?RWFybENQaGlsbGlwcw==?=

Can anyone help me with how to code the two subforms to pick up all
members of the other two major tables? Any help would be appreciated.

I am not at all sure that I know what your data structure is:-


One version is:

People -< Attend >- Events
Sponsors -< ContributeTo >- Events
People -< AreCustomersOf >- Sponsors

This implies that People can be CustomersOf Sponsors, regardless of
whether they Attend the Events that those sponsors Contribute to. Etc.

Another version is:

People -< View
Sponsors -< View
Events -< View

This means that People can View a Sponsor at an Event, but can view a
different sponsor at another event. You won't know that a sponsor
contributed to an event unless someone views them there; and you won't
know that a person went to an event unless they view one (or more) of the
sponsors.

There are other, more sophisticated, models depending on your real world.
Talking about subforms and relationships does not help unless you really
understand what is going with the real things and how your users process
them.

Hope that helps


Tim F

 

[EarlCPhillips]

The structure intended is to view the People table, the Event table and the
Sponsor table, viewing one member at a time for each table. Also displayed
with each member is a list on subforms of the associated members of the
other two tables. Displaying the People info for a particular person can be
a list of events they help with and a list of sponsors they are associated
with (a person can help at many events, such as a recycling event and a
neighborhood get together for cleanup, and simultaneously be associated with
many sponsors, such as Glenwood Lions Club and Glenwood Neighborhood
Association.) Since there is a many-to-many relationship between people and
events, there must be an intermediary table between them, holding the ID of
the person and the ID of the event, so all events that a particular person is
associated with can be differentiated from the events that another person is
associated with, especially since the people can both be associated with the
same event, but may not necessarily be. There also must be an intermediary
table between people and sponsors holding the ID of the person and the ID of
the sponsor and another between sponsors and events.

Therefore there are three major tables containing the data for the members
and three intermediary tables with the ID of both members of the relationship
so that one can find the other members of the relationship no matter which
member of which major table one starts from.

This just keeps track of which sponsors and/or people have contributed the
most to environmental events so there can be recognition for their
participation. I hope this clarifies the structure.

Earl Phillips
Ex-mainframer Learning Access To Help Non-Profits

 

[Tim Ferguson]

=?Utf-8?B?RWFybENQaGlsbGlwcw==?=

This just keeps track of which sponsors and/or people have contributed
the most to environmental events so there can be recognition for their
participation. I hope this clarifies the structure.

What you have described is the first version that I suggested (FWIW, this
is fourth normal form as I understand it).

From the point of view of the users, it seems there are three different
processes (browse Events details, browse People details, browse Sponsors
details) so I would build three forms for them to do it with.

When you have specific questions to ask, then a bunch of querydefs with
associated reports will tell you the answers.

Hope that helps


Tim F