Tiered Team Structure

[NathanSavidge]

Hi

I have a database where there are several teams. Teams can exist on their own, or be a sub team of a team, or be a sub team of a sub team. Currently i am running at 3 tiers, so i can expect data like this

Report

Team A Member A
Team B Member B
Team C Member C
Team D Member D

In this case Team A is a team, Team B and C are sub teams of A, and Team D is a sub team of C. I have coded a MI sub to take the data from these 3 tiers, using a different recordset for each tier. I would like to have this so that there can be a user defined number of tiers, and i can use a loop to do this.

I have tried my table set up as follows, for the above

ID Team Name Sub Team Parent Tier Level
1 Team A FALSE 0 0
2 Team B TRUE 1 1
3 Team C TRUE 1 1
4 Team D TRUE 2 2

I have tried getting the main team, and then using a for next loop, for intTier = 1 to 2, hovewer this works fine, for 2 tiers, but not for x tiers, as i need to show as Report above, not as Tier 0, then Tier 1, then Tier 2 i need to show Tier 0, its sub teams being tier 1, then their sub teams being tier 2, and then thier sub teams being tier 3 and so on. Can any one advise where to look or how to set up team table/SQL to do this. I want to do from VBA with a loop for intTier =1 to number of tiers.

TIA

 

[BrokenBiker]

Have you looked at tree-views? They might help out, although they're form-based, not reports.