I am looking to track attendance of multiple sets of people. The setup is as follows:
- I have 3 tables: "Athletes," "Connections," and "Business," which contain the contact information (We'll call these, "Main Tables")
- I have 1 table, "Group," which contains the list of participants from each of the Main Tables, as well as the Group Name and Group Leader. Each of the Main Tables has a relationship created to this table (one Group has many Athlete/Business/Connections, but there is only one Group associated with each Athlete/Business/Connection)
- I have 1 table, "Attendance," which contains a Multi-Select dropdown of names for Athlete, Connections, and Business. This field also does a lookup on the "Group" table to find the Group Leader after you select the Related Group from the dropdown option. This table tracks the attendance of each event within each Group.
- I want to track the attendance of each of theses groups within their respective table (I.e., I can click into "Athletes" table on a specific person and see how many times "Joe Athlete" has attended a recurring event.)
- One Athlete/Business/Connection can have many Attendances, and one Attendance can have many Athlete/Business/Connections. To get around this many-many relationship, I'd like to be able to add the Attendance within the "Group" table.
- One Athlete/Business/Connection can have only one Group.
- One Attendance can have only one Huddle Group.