Counting Across

[cable]

I have a organizational list of people and the people they report to.

I've expanded it across so you get the manager trail ie
person1, 1st mgr, 2nd mgr
person2, 1st mgr, 2nd mgr, 3rd mgr, 4th mgr
person3, 1st mgr, 2nd mgr, 3rd mgr

Now I really want to know how many managers each person has, ie count across until I get a null...but I can't remember how to do it.

I've got a feeling I need a piviot table, but i'm using acc97.

I could write a simple vb func to do this but I thought(hope) there was a simpler way.

 

[maxmangion]

how many fields do you have for the managers ?

you should only have one, and there would be no need to count across!

 

[cable]

huh? I've got 7 possible levels of management.

The data starts out as a single list, of everyone and their immediate manager (who's also in the list), I've done a query that expands this across providing a manager trail for each employee.

 

[maxmangion]

it appears that 1 employee can have several managers and 1 manager can have several employees, therefore you have a many-to-many relationship.

You should create a third table using the primary key of the employee and the primary key of the manager. This way your data would be normalised and in this manner you can count the amount of managers to a single employee very easy by using a total query.

If you take a look at the Sample databases there is a good post on how to use many-to-many relationship!

 

[Mile-O]

You only need the one table.

i.e.

tblEmployees
EmployeeID
Forename
Surname
ManagerID

Now, in the relationships window:

• Add tblEmployees
• Add tblEmployees again. The second will be called tblEmployees_1

Relate ManagerID (tblEmployees_1) between EmployeeID (tblEmployees) and you have created an unending hierarchy. None of this seven-tier limit rubbish.

 

[cable]

Thats the format I have (which is forced upon me because its imported from SAP)

And in the query thats what i did, cept I added it 7 times to get the full chain of management.
If this is wrong (and I knew it probly was but couldn't think of another way of getting to the result) how do I look at this table and say person A is ultimately managed by person C? or person C has x people immediatle reporting to him, and y people in total (ie if you follow the tree down)

I have a number of stats to generate to do with head count spreads across the different levels of management. Its currently done in excel with lots and lots of pivot tables, and I'm not sure if I can do them in Acc97.

 

[Mile-O]

This is what I meant: