Need guidance on setting up lookups and reports with a many-to-many setup

Question

I have the following many-to-many scenario: an event can have multiple participants and a participant can have many events.

I set up three tables:
1. Participants
2. Events
3. Participant-Event Join

1 and 2 each have a one to many relationship with 3.

I have another tables for Requests. I need to be able to select multiple participants on the request form and have certain fields from the participants table displayed in an embedded report on the Request form.

I also want to display all events a participant has participated in on the participant form and all participants who participated in an event on the event form.

How do I go about doing this? Do I need to add a one to many relation ship between the Requests table and the Join table? E.g. have the join table join three other tables?

------------------------------
Paul EASTON
------------------------------

MarkShnierYou · Answer

Something is confusing in your question.

for this question here
I also want to display all events a participant has participated in on the participant form and all participants who participated in an event on the event form.

You already have the set up for this in place and when you made those relationships to the drawing table the relationships would have created a report link field so you can embed the Participant-Event Join record on both the Participants Form (showing columns for the Event Name) and also embed the Participant-Event Join records on the Events Form (showing columns for the Participant Name).

Then separately you ask this

I have another tables for Requests. I need to be able to select multiple participants on the request form and have certain fields from the participants table displayed in an embedded report on the Request form.

You would just to the same thing you did for Events by setting up a third table called Request Participants Join.

(unless I'm not understanding and in fact you want to select not multiple Participants but multiple Participant Event Joins for a Request. In which case you still need a middle join table between Participant Event Joins and Requests.)

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

Sign up

Login to the community