hierarchy in one table

[Jim T]

I have a single table that has an employee and the manager they report to.
This table consists of all employees regardless of management level. Since I
have this table with the employee/manager relationship. How can I query to
show me the hierarchy of a high level manager. For example a GM has four
directors who each have two managers and the managers have 6 employees.
Iwould like to get a query that shows that this GM has a total of 48 persons
underneath him.

 

[Dale Fye]

Jim,

My guess is that you are going to need to write a recursive function that
you pass the EmployerID to.

In that function, define a static variable, intEmpCount.

Then create a recordset where the SQL select all of that managers employees
and loops through the list of employees, passing the EmployeeId to the next
recursion of the function. As each employee is processed, you increment the
intEmpCount variable. You would call this function in a query, or code like:

me.txt_EmpCount = fnEmpCount(me.txt_ManID, True)

Untested air code follows:

Public Function fnEmpCount(ManagerID as long, _
Optional ResetCount as boolean =
false) as integer

Static intEmpCount as integer
Dim strSQL as string
Dim rs as DAO.Recordset

if ResetCount then intEmpCount = 0

strSQL = "SELECT EmpID FROM yourTable " _
& "WHERE ManID = " & ManagerID
Set rs = currentdb.openrecordset strsql,,dbfailonerror

While not rs.eof
intEmpCount = intEmpCount + 1
Call fnEmpCount(rs("EmpID"))
rs.movenext
Wend

fnEmpCountExit:
if not rs is nothing then
rs.close
set rs = nothing
endif
fnEmpCount = intEmpCount
Exit Function

fnEmpCountError:
msgbox(err.Number & vbcrlf & err.Description, _
vbOKOnly, "Error in fnEmpCount")
Resume fnEmpCountExit
End Function

HTH
Dale