More Than 1 Lookup Needed

[dmd]

I have a table in Access that contains 10,000 records with Emp ID, Supervisor Name, & Supervisor ID on one row. I need to find the supervisor for each Emp ID then take their supervisor and look up that supervisor's supervisor.

Emp ID	Supervisor Name	Supervisor ID
999999	Tst Name	888888
888888	Tst Name2	777777

So in this table, I need to take 999999 and bring back the result of 888888.
I then need to take 888888 and bring back the result of 7777777 and so on. Can this be done in a query or various queries or is it something that would require code?

[JoeM]

You do a "self-join" (joining the table to itself) using table name aliases.
We will do a left-join so that it will not drop any records if there is no match.

The SQL code for that would look something like (assuming table name is "Supervisors"):

SELECT Supervisors.[Emp ID], Supervisors.[Supervisor Name], Supervisors.[Supervisor ID], Supervisors_1.[Supervisor ID]
FROM Supervisors 
LEFT JOIN Supervisors AS Supervisors_1 
ON Supervisors.[Supervisor ID] = Supervisors_1.[Emp ID];

To build this in the Query Builder, simply add your table to the query twice, then connect the "Supervisor ID" field from the first one to the "Emp ID" field in the second one, and change the relationship to a Left Join so the join arrow is pointing to the "Emp ID" field.
Then return all the fields from each table that you want.

For more levels, just repeat the steps adding more copies of the table to the query, and joining the "Supervisor ID" from the second table to the "Emp ID" in the third table, etc, etc.

[dmd]

That worked PERFECTLY! Thank You!!!

[JoeM]

You are most welcome!