In a database describing processes, tasks and who does them (roles) I have several tables:
A has a unique number for each task It also has a portal showing the individual or group role (which is stored in :
B has the task unique number, and the stored value for the individual/group. When a new task is created in A, one record is created in B for each selected role. B also has a portal showing the 'actual role' which is stored in C.
C has two relevant fields, and is the way in which the names of those individuals who make up the group can be revealed. The fields are Role/Group and Actual Role. When the individual is named in their own right, not as a member of a team, the values in these two fields are the same.
There are multiple records in B for a given task.
My problem is that when I report (in : wishing to display athe actual roles involved in each task, for groups it only displays the first actual role.
I beleive that I need to create a new table, containing the Task UID and Actual Role (one record for each combination of Task UID and Actual Role) but I've been stuck on this for so long that I can no longer see how to do this, or how to populate it with the existing data (B contains 8500 reords, and some of the groups referenced contain over 10 members, so I don't want a manual process...)
I know the answer is simple but I just can't see it.