Reports to Employee Report

[Scott C]

I have a table 'tbl_employees" with the following field:

EmployeeID (Primary)
Employee Name
Email
ReportsToEmail
.... and other fields that aren't relevant

I need to be able to run a query that show ALL employees that report up to a
specific person. It is fairly easy to do a report that show who reports
directly to a specific person, but I need to find all employees that are in
that organization.

For example, if a company had the following hierarchy,
CEO
-VP1
--GM1
---controller
----asst controller
-----staff accountant
-----staff accountant
-vp2
--gm2
---director of marketing
etc

I want to find out who reports to the GM1 (Controller, Asst Controll, Staff
Acct1, Staff Acct2)

I found Allen Browne's writeup on Self-Joins
http://allenbrowne.com/ser-06.html which is close to what I want. That
example allowed you to see all the horses ahead of him in the creation
process.

What I need to know is how many horses a particular horse is responsible for
creating , either directly (sons and daughters) or indirectly (grandkids,
great grandkids, great-great grandkids gre..........etc).


Any suggestions?

Thanks in advance

 

[Arvin Meyer [MVP]]

IIRC, The Northwind sample database that comes with Access has a reports to
hierarchy. Have a look at that and see if it meets your needs:

From the menubar, click on Help >>> Sample Databases >>> Northwind Sample
Database