I have written a macro which shows management structure in my company from the Top manager to simple employees.

The database Table looks like this that: I have one column Emplid which includes all employees in the company (Top manager, management and simple employees). Next columns are Last Name, First name of the employees. One column with Supervisor ID (Managers) which each employee has. Next column is HR Status where I have a information if a employee is still active or on Leave and Absence or International Assignment and Terminated or deceased.

I want that my macro will show the whole structure but only employees who have the status Active and Leave on Absence and International Assignment.

The Terminated and deceased shouldn't be in the extract.

Like here: Top manager--> Only Active Directors ---> Only active managers ---> Only active team leaders ---> only active Supervisors --> Only active employees.

The issue is that I want to use a SQL query and I don't know how to bite this.

I'm using a Excel Workbook as a Data Base.

I have created such macro but I'm weak in SQL and I don't know how to write good the query to get what I want.

Thank you for your help!

Public Sub SHOW_ALL_MANAGEMENT_LEVELS()
Application.ScreenUpdating = False


x = InputBox("Enter the Bridge ID of the Country Zone Head", "Hello!")
Set w = Application.FileDialog(msoFileDialogFilePicker)
w.Show

With w
For Each w In w.SelectedItems
fileName = w
    If fileName = "" Then
    Exit Sub
    End If
Exit For
Next w
End With


Set objConn = New ADODB.Connection
    cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=" & fileName & ";" & _
               "Extended Properties=Excel 12.0"

objConn.Open cnStr

    Set objCat = New ADOX.Catalog
    Set objCat.ActiveConnection = objConn

               For Each tbl In objCat.Tables
               sSheet = tbl.Name
                    If sSheet <> "Sheet" Then
                    sSheet = sSheet
                    Exit For
                    End If
               Next tbl

query = "SELECT L1.[Supervisor ID] AS N0, L1.[Supervisor First Name]+' '+L1.[Supervisor Last Name] AS [NAME]," & _
        "L1.[Emplid] AS N1, L1.[First Name]+' '+L1.[Last Name] AS [NAME 2], L1.[HR Status] AS [HR Status N1]," & _
        "L2.[Emplid] AS N2, L2.[First Name]+' '+L2.[Last Name] AS [NAME 3], L2.[HR Status] AS [HR Status N2]," & _
        "L3.[Emplid] AS N3, L3.[First Name]+' '+L3.[Last Name] AS [NAME 4], L3.[HR Status] AS [HR Status N3]," & _
        "L4.[Emplid] AS N4, L4.[First Name]+' '+L4.[Last Name] AS [NAME 5], L4.[HR Status] AS [HR Status N4]," & _
        "L5.[Emplid] AS N5, L5.[First Name]+' '+L5.[Last Name] AS [NAME 6], L5.[HR Status] AS [HR Status N5]," & _
        "L6.[Emplid] AS N6, L6.[First Name]+' '+L6.[Last Name] AS [NAME 7], L6.[HR Status] AS [HR Status N6]," & _
        "L7.[Emplid] AS N7, L7.[First Name]+' '+L7.[Last Name] AS [NAME 8], L7.[HR Status] AS [HR Status N7] " & _
        "FROM ((((([" & sSheet & "] L1 " & _
        "LEFT JOIN [" & sSheet & "] L2 ON L1.[Emplid] = L2.[Supervisor ID]) " & _
        "LEFT JOIN [" & sSheet & "] L3 ON L2.[Emplid] = L3.[Supervisor ID]) " & _
        "LEFT JOIN [" & sSheet & "] L4 ON L3.[Emplid] = L4.[Supervisor ID]) " & _
        "LEFT JOIN [" & sSheet & "] L5 ON L4.[Emplid] = L5.[Supervisor ID]) " & _
        "LEFT JOIN [" & sSheet & "] L6 ON L5.[Emplid] = L6.[Supervisor ID]) " & _
        "LEFT JOIN [" & sSheet & "] L7 ON L6.[Emplid]=L7.[Supervisor ID] " & _
        "WHERE L1.[Supervisor ID] = '" & x & "' AND L1.[HR Status] <>'Terminated' AND L1.[HR Status] <>'Deceased';"


    Set rs = New ADODB.Recordset

    rs.Open query, cnStr, adOpenUnspecified, adLockUnspecified

    Cells.Clear

Dim cell As Range, i As Long
Range("A4").CopyFromRecordset rs
    With Range("A3").CurrentRegion
    .Select
        For i = 0 To rs.Fields.Count - 1
            .Cells(1, i + 1).Value = rs.Fields(i).Name
        Next i



    .Cells.Select

    .EntireColumn.AutoFit
    End With

    rs.Close
    Set rs = Nothing
Application.ScreenUpdating = True
End Sub