How to List direct and indirect reports of a colleague in access or excel

I have the below/attached employee table in an access database.  I need a query to find all the direct reports and indirect reports of a particular employee

EmpID      Fname      Lname      MngID
1      Fred      Flinstone      
2      Linda      Thompson      1
3      Sam      Adams      1
4      Jason      Williams      1
5      Michael       Jordan      2
6      Katey      Perry      5


If I wanted to find all the direct and indirect reports of EmpID 2,  it should return michael and Katey,   Excel or access should be ok


This is what I want to do but in access SQL Query to find all the subordinates of a superior
Dennis MatthewsAsked:
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.

Shaun VermaakTechnical SpecialistCommented:
You can add this into column E and copy down
=IF(ISNA(VLOOKUP(D2;A:C;2;FALSE));"Big Boss";VLOOKUP(D2;A:C;2;FALSE))

Open in new window

Theo KouwenhovenApplication ConsultantCommented:
Please specify "direct and indirect reports of EmpID 2"
John TsioumprisSoftware & Systems EngineerCommented:
If you want to get hierarchical data via Access SQL then you need to study Celko's SQL Trees and Hierarchies in SQL for Smarties
For a quick reference here are 2 kind of explaining articles
on this subject : 1 , 2
Usually this kind of queries in Access are handled by a recursive function which populates a temporary table
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Dennis MatthewsAuthor Commented:
@Theo Kouwenhoven

In the example above, Michael is a direct report of Linda.  Katey is an indirect report of Linda.  Katey reports to Michael who reports to Linda.
Fabrice LambertConsultingCommented:
Well, this isn't doable the usual way.
But it can be done, as John suggested, with an nested set model.
The article below might explain it better:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

As for your implementation, we'll use a mix of both traditional model and nested set model:
Traditional model to find direct subordonates.
Nested set model to find indirect subordonates.
Create a table named Employee As Follow
|---------------------------------|
|             Employee            |
|---------------------------------|
|  ID, Integer, PK, Autonumber    |
|  Firstname, String              |
|  LastName, String               |
|  ManagerID, Integer             |
|  lft, Integer                   |
|  rgt, Integer                   |
|---------------------------------|

Open in new window

Next insert your data in the table:
|---------------------------------------------------|
|                     Employee                      |
|---------------------------------------------------|
| ID | Firstname | LastName  | ManagerID |lft | rgt |
|---------------------------------------------------|
|  1 |      Fred | Flinstone |          |   1 |  12 |
|  2 |     Linda |  Thompson |        1 |   2 |   7 |
|  3 |   Michael |    Jordan |        2 |   3 |   6 |
|  4 |     Katey |     Perry |        3 |   4 |   5 |
|  5 |       Sam |     Adams |        1 |   8 |   9 |
|  6 |     Jason |  Williams |        1 |  10 |  11 |
|---------------------------------------------------|

Open in new window

Finally, the queries.
To find all the subordonate of a manager based on his/her ID (ID=2 in this case):
SELECT	Node.FirstName,
		Node.LastName
FROM	Employee AS Node,
		Employee AS Parent
WHERE	Node.lft > Parent.lft
  AND	Node.lft < Parent.rgt
  AND	Parent.ID = 2
ORDER BY Node.lft;

Open in new window

To find the direct subordonate of an employee based on his/her ID:
SELECT	Employee.FirstName,
		Employee.LastName
FROM	Employee AS Manager INNER JOIN Employee
			ON Manager.ID = Employee.ManagerID
WHERE	Manager.ID = 2;

Open in new window

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
Dennis MatthewsAuthor Commented:
@ Fabrice Lambert is there any quick way to convert to the nested set model for a large data set?
Fabrice LambertConsultingCommented:
Do you mean migrate data from an existing table ?

Can you refine your need ?
John TsioumprisSoftware & Systems EngineerCommented:
A rather excellent example of solution "hijacking"...i must remember that people love solutions on a gold platter than directions...next time instead of providing link i just copy-paste...simple and point-guaranteed.
Fabrice LambertConsultingCommented:
@Denis:
It will be great to award John points as well.
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
VB Script

From novice to tech pro — start learning today.