INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query Help

Query Help

(OP)
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

RE: Query Help

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.

RE: Query Help

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

RE: Query Help

(OP)
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

RE: Query Help

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

CODE --> sql

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

CODE --> sql

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

RE: Query Help

(OP)
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!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close