AnsweredAssumed Answered

summarize/isolate many to many records, Do I need 6 tables?

Question asked by bnmohr on Feb 25, 2018
Latest reply on Feb 25, 2018 by bnmohr

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

2/2/18     Brian    

                              Scott, Jill    

                                                  Jane, Bob


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.


Many thanks!