Trying to create an Update query based on HR data to find upline V

  • Thread starter Thread starter Ringking1110
  • Start date Start date
R

Ringking1110

Hi All, looking for some advice. I have an HR table that contains employee
information but does not contain management chain info. Basically i am trying
to determine who the employees upline VP is. The fields i have to work with
are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would
be to check the employees' manager and if the manager is a VP (based on job
title), return the manager's name to a field called [VP]. If the manager is
not a VP then check that manager's manager, so on and so forth until a VP is
found.

Any ideas would be much appreciated!!
 
A person can have only one manager (in most situations) and the manager is
also an employee. Your table should have a primary key, say an autonumber.
Add a new field, number long integer, called MGR.
Run this query --
UPDATE YourTable INNER JOIN YourTable AS YourTable_1 ON YourTable.Manager =
YourTable_1.FName SET YourTable_1.MGR = [YourTable_1].[PersonID];

In the relations window create a self-join from [YourTable_1].[PersonID] to
[YourTable_1].[MGR].

Using this means you can display your management chain.
This query will display it. You need to add the appropriate number of
levels --
SELECT YourTable.FName, YourTable_1.FName AS First_Level, YourTable_2.FName
AS Second_Level
FROM (YourTable LEFT JOIN YourTable AS YourTable_1 ON YourTable.MGR =
YourTable_1.PersonID) LEFT JOIN YourTable AS YourTable_2 ON YourTable_1.MGR =
YourTable_2.PersonID
WHERE (((YourTable_1.FName) Is Not Null) AND ((YourTable_2.FName) Is Not
Null));
 
Back
Top