Need to create a hierarchy of direct and indirect reports

[Dmcleod]

I created two queries. First one takes every EMP ID and every Manager ID. Second uses the Manager ID from QRY1 and links to EMP ID from the original table to pull in that managers manager ID. In testing the data I am looking at Manager 10592. In QRY1 that manager has 6 reports. I need to be able to keep creating queries so I end up with a list of managers from the WAR table and every emp that reports up to that manager, whether direct or indirect (someone reporting to a manager that reports to another manager up to the complete list of managers from the WAR table)

I am not getting the results I thought I should. Any ideas?

[recyan]

Not sure if below is what you are looking for, all the same just check it out :

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

Thanks

[Dmcleod]

Not sure if below is what you are looking for, all the same just check it out :

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

Thanks

That works for single levels of management. If a Sr Manager has 2 mgr reports, the 2 mgr reports each have 5 supervisor reports who in turn have multiple non-management reports, then I need a list of all the reports under that 1st Sr Manager, no matter who the direct report is ...

[Dmcleod]

That works for single levels of management. If a Sr Manager has 2 mgr reports, the 2 mgr reports each have 5 supervisor reports who in turn have multiple non-management reports, then I need a list of all the reports under that 1st Sr Manager, no matter who the direct report is ...

Name	Code	MGR
Sandeep	10	Katherine
Robert	20	Sandeep
Marica	30	Robert
Katherine	40
Gabriele	50	Sandeep
Victor	60	Katherine

I need a report so I can pull the managers name and get the following results:
Sandeep over Robert, Marcia and Gabreiele.
Robert over only Marica
Katherine over Sandeep, Robert, Marcia, Gabriel and Victor

[recyan]

Till someone comes along, Not much in to reports & for that matter, vba, just check if below is of some help :

Public Function GetReportees_1()
    Dim dbs As Database
    Set dbs = CurrentDb
    
    Dim strsqla As String
    Dim rsta As DAO.Recordset
    
    strsqla = "SELECT EmpName, Mgr FROM tblEmpMgrs WHERE (((Mgr) Is Null))"
     
    Set rsta = dbs.OpenRecordset(strsqla, dbOpenDynaset)
    Do While Not rsta.EOF
        managerb = rsta![EmpName]
        Debug.Print managerb
        GetReportees (managerb)
        rsta.MoveNext
    Loop
End Function

Public Function GetReportees(Manager As String)
    Dim strsql As String
    Dim rst As DAO.Recordset
    Dim ManagerD As String
    
    Dim dbsa As Database
    Set dbsa = CurrentDb
    
    ManagerD = Manager
 
    strsql = "select EmpName from tblEmpMgrs where [Mgr] = '" & ManagerD & "'"
 
    Set rst = dbsa.OpenRecordset(strsql, dbOpenDynaset)
    Do While Not rst.EOF
        If (rst.EOF) Then
            Debug.Print "End of Records"
        Else
            managera = rst![EmpName]
    
            Debug.Print " - " & managera
            GetReportees (managera)
        End If
        rst.MoveNext
    Loop
End Function 

Thanks