Access Query to Sum Direct Reports


Attached is a sample database with an employee table and query which shows a reporting hierarchy.  What I would like is a query to see a list of managers and their count of direct reports.  So ideally I could see which supervisors have more than a certain number of direct reports by position.  Any assistance would be appreciated.

Data SpartanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesSenior Application DeveloperCommented:
Ron, your attached database file contains a link to an Excel spreadsheet called 'tbl_sample', but it does not contain the actual data.  If you'll post a file with the data, I'll be happy to build a query for you.  :)
Data SpartanAuthor Commented:
Thanks, Paul!   - I converted it over to a local table but went ahead and attached the Excel file.
Paul Cook-GilesSenior Application DeveloperCommented:
This query will return the people in tbl_sample, and the count of the rows that have the person's [Employee Number] in their [Manager ID] field:

SELECT [Employee Name], [Employee Number], Department, Position, DCount("*","tbl_sample","[Manager ID] = " & [Employee Number]) AS Subordinates
FROM tbl_sample

If you want to see only the people that have subordinates, you can add a criteria

> 0

to the Subordinates column.

Let us know how that works for you.  :)
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Data SpartanAuthor Commented:
Paul - Many thanks!  I believe that does the trick.  I do have a question though - how difficult would it be to get the full count of direct and indirect subordinates?  For example, Janet King, who is the CEO, has 18 direct reports but in fact all employees in the table eventually report up to her.  How could the query be modified to get that full count?
Paul Cook-GilesSenior Application DeveloperCommented:
I know I've built similar functionality in the past... I believe my solution was to add a "Hierarchy" field, and then loop through the records, adding the ManagerID of the person whose EmployeeID was in the manager ID field of the current record, and so on, until I ran out of further levels.
After Hierarchy was populated with the series of IDs, I could count the number of rows where the Employee's ID was in the Hierarchy field, and that was the count of indirect reports.

Let me noodle with your data and see if I can make that work here... but if someone else has a more elegant solution, please chime in.
Data SpartanAuthor Commented:
Clever idea - I really appreciate your help.
Paul Cook-GilesSenior Application DeveloperCommented:
A modified copy of your database is attached.

I built a sub in Module1 that loops through all the records in tbl_sample (except those with 30 in the ManagerID field), grabs the manager ID of the person whose employee ID is in the manager field, drops it with a delimiter (comma space)  in the Hierarchy field, and then looks for the manager of the person whose employee ID was just dropped.  When found, that employee ID is added to the Hierarchy field, and the loop repeats until the manager ID is 30 (which is the top of the hierarchy).
There's a Debug.Print line that drops the SQL string in the Immediate pane so you can see what's going to happen before it does.  If you need to run this against a bigger table, you probably want to comment out that Print line as it will slow down the execution by a measurable amount.
Then an update query removes the last delimiter from the strings in the Hierarchy field.

The CountSubordinates query has two DCount functions;  one that counts the ManagerID fields that match the Employee Number, and another that counts all the rows in the table that have the EmployeeNumber somewhere in the Hierarchy field.

Look through this, and let me know if it's what you need.  :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Data SpartanAuthor Commented:
Perfect!  Thank you so much for all of your assistance.  You've been very gracious with your time and expertise.  Cheers!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.