I am building a daily schedule for a music group that is essentially a calendar to which a person can be assigned to work a day.
Tables all related by unique ID's but referenced here by fields for readability
Suppose you have the following records in PeopleOnDays:
Date (aka. id_Days) Name (id_People) Role (a field in PeopleOnDays)
2/2/18 Brian Lights
2/2/18 Scott Sound
2/2/18 Jane Other
2/2/18 Jill Sound
2/2/18 Bob Other
What I am seeking is to summarize all of the assignments into a single row of the Days table so that a year's schedule is not 10 pages long.
So one row would appear columns Date, Lights, Sound, Other:
2/2/18 Brian Scott, Jill Jane, Bob
I can use a summary field to retrieve every record in the join table, but I don't see any way to isolate records by role for the three columns I want. I can use a portal to filter records, but then each record is a separate line and I can't create comma separated lists. I believe it's the same if I do a summary based on PeopleOnDays- each role would have to be another row in the report so I would get
My current path forward would be to create 3 sets of join tables and people TO's where the join tables represent the different roles, so PeopleOnDaysLights, PeopleOnDaysSound and PeopleOnDaysOther. Then I can use a summary field and only see the people with a certain role. I don't really have a problem with this other than my solution already seems overflowing with tables so I feel like I'm overly reliant on building extra relations to solve my problems.