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
Bookmarks