Multiple relationships between two tables

[PietC]

Hello all. I have a staff list with unique IDs. Each staff member can be a team member, a team leader or a supervisor. To make it slightly more complicated, an individual can be a member of one team and a leader for another – but let’s park that for now.
</SPAN>
I need to produce a team sheet showing the team name, its members, leader and supervisor (and their details). Previously I’ve used an “allocation” table and copious VLookup formulas to derive the information I need, but I’d now like to use the Excel 2013 data model (no powerpivot as I don’t have the right edition).</SPAN>

I now have two tables: tblStaffList and tblAllocation. My allocation table has 4 columns: team name, members, leader and supervisor. Apart from the first column, the contents are all staff IDs. However, as any two tables can only have one active relationships between them, I can only link either members or leaders or supervisors, but not all three.
</SPAN>
Can anyone please suggest a practical approach? </SPAN>
Many thanks. Piet</SPAN>

 

[PietC]

OK - I've found a quick-and-dirty solution by simply copying tblStaffList twice, as tblStaffList02 and tblStaffList03 respectively, once each for team leaders and supervisors, then creating the necessary relationships with these copies. This works, but it seems a bit clunky! Is there a more elegant method?

Piet