Can I VBA Loop through a chain of command? (who reports to who)...

I have 2 columns Employee ID and Manager ID... how is it possible to run a query which traces for employee #1's manager and his manager etc... and keeps going until it hits the CEO (who is #4)  who reports to no one?
so the results would look like this:
Employee      Manager
2      9
9      88
88      4
Next…            
Employee      Manager
5      7
7      56
56      4

Next...
computeriderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jerryb30Commented:

Is there a top number of levels you may see?
0
Rey Obrero (Capricorn1)Commented:
using the northwind employees table

select
  Emp.employeeid
  ,Emp.lastname as Employee
  ,Emp.reportsto
  ,Mgr.employeeid
  ,Mgr.lastname as Manager
from employees Emp left outer join employees Mgr
on Emp.reportsto  = Mgr.employeeid

0
computeriderAuthor Commented:
no -  just a list of who reports to who so it doesn’t distinguish titles – you either have someone reporting to you or you don’t…which is why I am guessing it has to loop until it hits the CEO
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

computeriderAuthor Commented:
Close but you this produces a simple who reports to who - I am trying to have the results look like this (these are based on the results of the Northwinds query)

so If I report to Buchanon tell me who my boss (Buchanon)  reports to, then who his boos reports to until we hit the top of the chain and grab the next guy...

Employee ID      Last Name      Reports To      Employee ID      Last Name
6      Suyama      Buchanan, Steven      5      Buchanan
5      Buchanan      Fuller, Andrew      2      Fuller
2      Fuller                  
Next Employee                        
1      Davolio      Fuller, Andrew      2      Fuller
2      Fuller                  
Next Employee                        
7      King      Buchanan, Steven      5      Buchanan
5      Buchanan      Fuller, Andrew      2      Fuller
2      Fuller                  
0
mbizupCommented:
Check out this question I asked a while back... An assembly is expanded from the top to the bottom, so that all of its parts are listed and subassemblies are expanded to show their parts....

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22105395.html

It seems analagous since you're dealing with-- a Top level manager, his/her subodinates, their subordinates, etc.  

Is this headed in the right direction?
0
computeriderAuthor Commented:
yes - i see this code:
**************************************************************************************************************************
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblMBIZ WHERE ID = '" & sID & "' OR ParentAssembly = '" & sID & "' ORDER BY ID")
    Do While rs.EOF = False
        If rs!ID <> sID Then
            ListAssemblies rs!ID, True
        Else
            Debug.Print rs!ID, rs!PN
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub
**********************************************************************************************************************
It loops until it hits EOF – how will it define EOF?

When it hits (example above) Andrew Fuller who has a null in reports to field? And thus starts with then next EMP ID? If so it could work…
0
mbizupCommented:
This actually works the opposite way...    So rather than answering the question "who is above me?",  it will give you a heirarchy from the top - down.  So entering "Andrew Fuller" would give you his subordinates, and their subordinates down to the lowest level.  Is that an acceptable alternative?
0
mbizupCommented:
You also *can* get the chain of command from the bottom to the top.  The inherent problem with this approach is that if you expand this to include other factors (such as projects worked on) where the same employee can have different managers based on project, the chain of command becomes ambiguous.  This is why a bottom-up approach would not work in the assembly parts list example I posted (any part could appear on several different assemblies).  A top-down approach eliminates that ambiguity.

If there is no chance that a single employee can have multiple managers, this bottom up approach (untested air-code) should work...

Function MySuperiors(MeID as long) '*** this will list the chain of command above an employee, based on ID
Dim SubordID as long
Dim rs as DAO.recordset
Dim I as integer
Set rs = CurrentDB.OpenRecordset("tblEmployees", dbopendynaset)
subordID = MeID
Do Until NZ(rs!ManagerID,"") = ""   '*** loop until there is no manager)
      rs.MoveFirst  '**** start the search at the beginning of the recordset
      rs.FindFirst "EmployeeID = " & SubordID
      if rs.NoMatch then
         MsgBox "No matching record found"
         exit Do
      end if
      debug.print "Subordinate ID: " & subordID & "     Manager ID: " & rs!ManagerID
      SubordID = rs!ManagerID '*** this manager becomes the subordinate at the next level
      I = I +1
      if I > 50 then exit Do           '*** Guards against infinite loops
next
end function


     
0
computeriderAuthor Commented:
mbizup - the above code looks like it - there is only 1 mgr per employee and no projects, etc so it is a 1 to 1 all the way to the ceo...

Now to produce a query where I can choose an individual from a combo box on a form and have a listbox show me who he reports to all the way up the ladder, do I build an sql query and attach it to the combo afterupdate event? If not how do I make this user available/friendly?

And do I need this line of code
************************************************************************
Set rs = CurrentDB.OpenRecordset("tblEmployees", dbopendynaset)
************************************************************************
If it used in a single database which has all my collection?
0
mbizupCommented:
>Now to produce a query where I can choose an individual from a combo box on a form....
Yes, you would use the after update event of the combo box to call a function similar to what I posted.  The code would have to be modified to add items to a listbox instead of printing the names in the debug window

>And do I need this line of code
Yes... That allows your table data to be manipulated in VBA

>If it used in a single database which has all my collection?
Can you elaborate on this by providing specific table and field names?  I'm guessing it is Something like this, but let me know the details specific to your application:

tblEmployees
-----------------
EmployeeID
EmployeeFullName
ManagerID

Also, if you haven't done this already, set up a combo box using the wizard, that displays the names employees, but returns the ID when one is selected.  This can be verified by adding this to the after update event of the combo:

MsgBox Me.cboEmployees

The message box should display a numeric ID when a selection is made.
0
computeriderAuthor Commented:
"Yes, you would use the after update event of the combo box to call a function similar to what I posted.  The code would have to be modified to add items to a listbox instead of printing the names in the debug window"

I am not experienced enough to write the function code whole myself...your table/field description is accuarate...but I am in Access 2K and  I get 2 errors...DAO is not recognized (so I put RS as recordset) and then it doesn't accept FindFirst as a known method - and then wants a FOR at the end...can you give me a better idea how the code should be structured?
0
mbizupCommented:
>DAO is not recognized
From the VBA editor's menu:
-Tools -> References
-Make sure that Microsoft DAO Version x.x is checked.
-If it is labeled as MISSING, uncheck it, and select the most recent version from the list
-Click OK to save and close the references window

Then use the original line I gave you...
Dim rs as DAO.recordset

Let me know if that cured that issue... I'll help you out with the code later on today.
0
computeriderAuthor Commented:
Got that fixed now it only bombs at the last NEXT with a compile error of "Next without For"
0
mbizupCommented:
Okay...

1. Add a listBox;  the row source is unimportant.  Call it lstManagers.
2. Add a combo box named cboEmployee,  like I described in my earler post to select an employee by ID.  Add the following code to that combo box's afterupdate event:

MySuperiors Me.Combo0, Me.lstManagers

3. Add a table:
 tblTempManagers
------------------------
ManagerID -- AutoNumber, Primary Key
ManagerName -- Text

This will hold the list of employee x's superiors (chain of command)

4.  Add the following code to a module (Save the module as modChainOfCommand)

Public Function MySuperiors(MeID As Long, lst As ListBox) '*** this will list the chain of command above an employee, based on ID
Dim SubordID As Long
Dim rs As DAO.Recordset
Dim I As Integer
Dim strManagerName As String

Set rs = CurrentDb.OpenRecordset("tblEmployees", dbOpenDynaset)
SubordID = MeID
CurrentDb.Execute "DELETE * From tblTempManagers" 'Clear temp table
lst.RowSource = ""               '** clear rowsource of listbox
lst.RowSourceType = "Table/Query"
'Add selected name as first entry in list
strManagerName = DLookup("FullName", "tblEmployees", "EmployeeID = " & MeID)
CurrentDb.Execute "INSERT INTO tblTempManagers (ManagerName) Values('" & strManagerName & "')"
Do Until Nz(rs!ManagerID, "") = ""  '*** loop until there is no manager)
      rs.MoveFirst  '**** start the search at the beginning of the recordset
      rs.FindFirst "EmployeeID = " & SubordID
      If rs.NoMatch Then
         MsgBox "No matching record found"
         Exit Do
      End If
      If IsNull(rs!ManagerID) Then Exit Do
      strManagerName = DLookup("FullName", "tblEmployees", "EmployeeID = " & rs!ManagerID)
      'Add manager to temp table
      CurrentDb.Execute "INSERT INTO tblTempManagers (ManagerName) Values('" & strManagerName & "')"
      SubordID = rs!ManagerID '*** this manager becomes the subordinate at the next level
      I = I + 1
      If I > 50 Then Exit Do           '*** Guards against infinite loops
Loop '***** (Changed 'next' to 'Loop')
lst.RowSource = "SELECT ManagerName FROM tblTempManagers ORDER BY ManagerID DESC"

rs.Close
Set rs = Nothing
End Function

0
mbizupCommented:
Correction:
The code in the combo's after update event should be:
MySuperiors Me.cboEmployee, Me.lstManagers
0
computeriderAuthor Commented:
Excellent!!!

This works now if I may ask a few questions so I understand what I have done before I close this question (no other board has understood my question but you apparently)

1.      Why the temp table? Is it because the results of the query are just to view, created dynamically and then discarded?
2.      I can rewrite the query to display titles and other related fields to display in the list box, right?
3.      and only 1 bug so far, if a manager’s name has a quote mark in  the name (example: O’Connor) it crashes here:

CurrentDb.Execute "INSERT INTO tblTempManagers (ManagerName) Values('" & strManagerName & "')"

How do I rewrite the line above to allow for such cases?

Again, I salute you sir…I’ve been asking for months…
0
mbizupCommented:
1.  (Why the temp table? ) Iterating through the recordset from the bottom level to the top gives you the chain of command in the wrong order.  A temp table with a sequential numeric ID field is a convenient way to store the chain of command.  When listbox's rowsource is set to the temp table, the order of the chain of command is corrected by reversing the sort order (in the rowsource: ORDER BY ManagerID DESC).

2.  (I can rewrite the query) Yes... you need to include the new fields in the INSERT statement to include them in the temp table, and also in the SELECT statement to include them in the listbox.

3. (and only 1 bug so far)  Try revising the code like this:

CurrentDb.Execute "INSERT INTO tblTempManagers (ManagerName) Values(" & chr(34) & strManagerName & chr(34) & ")"

3.5 (I salute you sir)  
thank you :-)   :-)     (but you are talking to a lady :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
computeriderAuthor Commented:
I thought when I wrote that "Wait - it may be a lady" but I got sidetracked!  What a great lessson for me to learn!!! I salute you, Ms.!!!
0
mbizupCommented:
Glad to help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.