Query Help 1

[ptrifile]

Let's say I have a table with 2 fields. They are "EmployeeName" and "Manager" name. Now I want to have a query that will show EmployeeName, Manager, and then the Managers Manager name. So i am trying to have 3 levels of who reports to who in one record. Can I do some sort of lookup in the query to lookup a persons manager to show the managers manager? Hope this makes sense as its hard explain for me.

Thank you,

Paul

 

[Andrzejek]

You can (probably) do it, but how to do it depends on your table's (tables') structure.
Do you have all of the data in one table? Or do you have 1 table for Employees, one table for Managers, and one table for - let's say - Supervisors?

If you have just one table, could you give some examples of data and the outcome that you want?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

You can use self joins multiple times. It might be easiest to work from the top down rather than the bottom up.

Do all employees have managers in the table except maybe the top dog?

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

Thanks Andy, I have just one table with all of the data. The table has a list of ALL employees and their direct supervisor/manager. What I would like to do is have a query that will show the employee and then 2 levels of management above. So for the second level of management it would have to query that persons direct supervisor and then query their supervisors supervisor. I will try and give an example below:

Existing table

EMployeeName supervisor
John Doe Bill Smith
Bill Smith Peter Rabbit

I would like the query to show the following:

EmployeeName Supervisor Manager
John Doe Bill Smith Peter Rabbit

Thank you for any help or suggestions.

Paul

 

[dhookom]

I believe your query would look something like:
Shouldn't "Peter Rabbit" also be in the employee list?

SELECT ExistingTable.EmployeeName, ExistingTable.Supervisor AS Boss, 
  ExistingTable_1.Supervisor AS [Boss Boss]
FROM ExistingTable LEFT JOIN ExistingTable AS ExistingTable_1 ON 
  ExistingTable.Supervisor = ExistingTable_1.EmployeeName;

OR

SELECT ExistingTable.EmployeeName, ExistingTable_1.EmployeeName AS Boss,
  ExistingTable_2.EmployeeName AS [Boss Boss]
FROM (ExistingTable LEFT JOIN ExistingTable AS ExistingTable_1 ON 
  ExistingTable.Supervisor = ExistingTable_1.EmployeeName) LEFT JOIN 
  ExistingTable AS ExistingTable_2 ON ExistingTable_1.Supervisor = ExistingTable_2.EmployeeName;

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

Duane, your first code worked perfectly and gave me the desired results! And yes, you are correct, Peter Rabbit also shows in the employee list. Thank you so much!!!