Ringking1110 -
The same structure would work, but field types could change things. I
assume that [Employee Number] is an integer, and that [Manager AD] is also an
integer that matches the manager's [Employee Number]. If that is the case,
then use the code below. I have changed it to use EENumber as an Integer,
which changes some delimeters, etc. This also returns an integer (because
this must be a recursive function). That means the final results will be the
[Manager AD] for the VP rather than the name. You can do a DLOOKUP for the
name, but that has to happen outside of this code, so I added a function (at
the bottom) to return the name instead of the ID. This function is called
GetVPName.
To use this, back up your data first, then start with a select query where
you one or both functions in column(s) something like this:
Employee VP ID: =GetVP([Employee Number])
Employee VP NameD: =GetVPName([Employee Number])
Validate that the data is what you expect. I could easily have a typo in
this, so compile it first. Option Explicit should be at the top of the
module, before any procedures. Once you get the data the way you want it,
then you can use an UPDATE query to populate the field in the table.
Public Function GetVP(EENumber As Integer) As Integer
' Will recursively check HR table to find VP in title
Dim EmpTitle As String
Dim MgrIDe As Integer
GetVP = 0
Do Until GetVP <> 0
EmpTitle = DLookup("[HR Job Title]", "Merged HR Data", "[Employee
Number] = " & EENumber )
If EmpTitle = "VP" Then
GetVP = EEName
Else
MgrID = DLookup("[Manager AD]", "Merged HR Data", "[Employee Number]
= " & EENumber )
If MgrID is Null Then
GetVP = -1
Else
GetVP = GetVP(MgrID)
End If
End If
Loop
End Function
Public Function GetVPName(EEID as Integer) as String
'This function will find the name of the VP for any employee number passed in.
'It calls the GetVP function to find the Employee Number of the VP, then
looks up the name.
Dim VPID as integer
VPID = GetVP(EEID)
If VPID > 0 Then
GetVPName = DLookup("[Employee Name]", "Merged HR Data", "[Employee
Number] = " & VPID )
Else
GetVPName = "No VP found for Employee Number " & EEID
End If
End function
--
Daryl S
Ringking1110 said:
Thanks Daryl. I would like to detail my scenario more accurately. I have a
table called "Merged HR Data". In this table I am trying to populate a field
named "FA AD" using the following fields, "Employee Number", "Manager AD",
and "HR Job Title". There will be no manual entry of data as I intend to use
the data existing in the table. The "Employee Number", "Manager AD", and "FA
AD" fields contain the same type of values (Active Directory accounts). The
goal is to determine the upline VP for an employee using the user's
management chain and what is contained in the "HR Job Title" field. Will the
structure of the sample you provided below change?
Daryl S said:
Ringking1110 -
You can create a function that will recursively look up through the
manager's until it finds the VP (or no manager). Note that since you are
using text strings, the chances of data error are great. For example if my
manager's name was Jane Doe (on my employee record), but her employee name
was Jane Q Doe, then this code would fail to find her. You can use this in a
query or on a form. Just pass in the employee's name.
Anyway, here is a quick sample - adjust for your table name.
Public Function GetVP(EEName As String) As String
' Will recursively check HR table to find VP in title
Dim EmpTitle As String
Dim MgrName As String
GetVP = ""
Do Until GetVP <> ""
EmpTitle = DLookup("[Job Title]", "tblHR", "[Employee Name] = '" &
EEName & "'")
If EmpTitle = "VP" Then
GetVP = EEName
Else
MgrName = DLookup("[Manager Name]", "tblHR", "[Employee Name] = '" &
EEName & "'")
If MgrName = "" Then
GetVP = "No Manager For " & EEName
Else
GetVP = GetVP(MgrName)
End If
End If
Loop
End Function
--
Daryl S
Ringking1110 said:
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!!
