You need 3 tables, Event, EventParticipant (join table) and Participant.
PK from event related to FK on EventParticipant.
PK from Participant related to another FK on EventParticipant
In it's basic set up, EventParticipant would be a portal on the event table, where you can add or lookup participants
How do you have it set up?
Hi. I'm going to presume you have not only multiple Events and multiple potential attendee Participant people, but also people who attend in multiple events. Therefore—and please pardon me if this is 'old hat' to you—you'll need an Attendee 'join' table to contain records that are at the very least every instance of one participant person attending one event. The Attendee table would have the event ID and the participant ID. A relationship from Participant to Attendee would show all the Events they've attended and a relationship from Event to Attendee would show all the participants of that one event.
In case you're more visually oriented, both Steve's and Erik's suggestion can be graphically displayed as
Event --< Attendee >-- Person
so an Attendee record has exactly one related Event and one related Person record (since it describes a combination of these entities), while both People and Event records have (potentially) many Events attended and People attending, respectively.
Yes, you are correct: Multiple Events and multiple potential attendee Participant people, but also people who attend in multiple events.
I've now created those relationships.
But the other information (address, phone #, etc) isn't populating into the Join table when choosing from a list of Attendees.
Also, First and Last name are separate fields, but I need a calculation field of Full Name (First & " " & Last) to be in the pick list. When I do that, not says field is not modifiable.
Something isn't right, or complete. I see on your graph all tables are showing many-to-many relationships.
Just adding a name/creating a record into the join table doesn't create a related record. What process are you using.
Take a look at the Event Management starter solution and pick thru how it creates records, and how the tabkes are joined.
No. there is not a many to many relationship.
Events: Event ID <> Events | Participants: Event ID
Events | Participants: Singer ID <> Participant: Participant ID
I am using a portal, and yes it does create a related record.
Please see the Layouts image showing that.
Here is the file. There is just sample data in it.
user name: admin
That did it!
I removed the unneeded fields from the join table, as you suggested. Also, changed the participant ID field back to the manually entered one — this is a unique number known to the participants and needs to be set.
Kudos for the phone # mask...that was in my plan. Saved me a bunch of work.