I'm hoping this has an easy fix because it's a pretty simple use case! I have a table where each record is a task. Each task may have multiple contributors. I have a field that indicates the month the task will be completed as well as a field that shows who the key contributors are (Multi-select field). I need to create a view where each contributor can see all the tasks they are to work on, further broken down by month. The problem is that using the grouping feature, there doesn't seem to be a way to have a single records included in multiple groups. For example, if Sally and Roger are both contributors on a Task 1 in September, I'd need that task to be included in Sally's list as well as Roger's list and NOT in an aggregated grouping called "Sally and Roger". How can I accomplish this? (my desired break down is below). Thanks for any ideas!
You will need to completely restructure your entire base as a "many-to-many relationship", which is an advanced database relationship structure.
A many-to-many relationship requires 3 tables (instead of 1 table or 2 tables).
Here is the breakdown of your 3 tables:
Table 1: Tasks
Table 2: Staff Members
Table 3: This is what's known as your "junction table" or "join table". Each record in this table links to EXACTLY ONE TASK (from the task table) and EXACTLY ONE STAFF MEMBER (from the staff table).
So, if a task has 3 staff members attached to it, you will need to create 3 different records in the "join table". Each of the 3 records will be linked to the SAME task, but each of the 3 records will be linked to a DIFFERENT staff member.
The "join table" is where you will do most of your data entry, and it is ALSO where you will also get the breakdown that you're looking for.
Thank you @ScottWorld - this is very enlightening. While I'm sure this solution will result in the view I need today, as work progresses and expands, it will also create a lot of training and upkeep for a small team with little bandwidth (or budget) to maintain this over time. What I will likely have to do is simply create a filtered view for each contributor so they can each focus on tasks relevant to them. With a team of 7-8, this is doable and likely simpler for them to understand and maintain. I really appreciate your help though!