3 file relationships

[bluedoor]

Hi all

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.

Paul

[CyborgSam]

Paul->

I'm not sure I understand what you're doing, so I'm guessing:

I think file A has tasks, and C has People. B has records with two fields with key values in A & C. So A & C are many-to-many relationships both ways.

If true, viewing the data may be easiest in either A or C, not B (simply since you may be able to control what is viewed more easily). You could make a portal in A that displays records from B. A calculation field in B referencing C through a relationship would put the value you need in the portal in B.

Does this make enough sense or do you need more details?

Sam

[bluedoor]

Thanks Sam -

I confess I'm still stuck. "A calculation field in B referencing C through a relationship would put the value you need in the portal in B": I already have a portal in B displaying values from C.

Are you suggesting I replace this with a calculated field? If so, how do I get the multiple values?

Hopefully -

Paul