Reporting lines

[Dave_cha]

Hi folks,

Another problem I'm hoping someone can help me with.

I have a table with two columns. The first contains a list of employee id's (one for each employee in the company) and the second contains the employee id to whom the employee in col1 reports to.

I would like to build a form with two list boxes. The first list box would contain a full list of employees. When an employee in list box 1 is selected, I would like the second list box to populate with all employee reporting into the selected employee (both directly and indirectly).

Filling Listbox 2 with those reporting directly is simple however I'm not so sure how to fill it with indirect reporting lines also.

I hope I'm making sense. By way of an example, if I select a company manager I would like to list all Asst. Mgr's reporting into that manager and in addition all supervisors reporting into those AM's, all employee's reporting into those supervisors etc. All this should be possible using the table mentioned above but my VBA skills are fairly rough still.

Any help with the code to build this list would be greatly appreciated. I imagine there's probably code out there somewhere already but I'm not sure what to search under.

Thanks, Dave

 

[llkhoutx]

Articulating your question more simplistically: You want to filter a listbox based upon a selected row in another list box.

With information regarding your "names" table, one could easily code what you want.

Selected listbox row is chronicled on this site. I can recall having previously posted such code here. Have you searched this site?

 

[ShiftyJaffa]

Hi Dave

As a suggestion although this might be a bit primative, I would try to create a temp holding table for your results which contained ID (Autonum) & just the EmpID.

In the 'On Click' event of the 1st box

1. Populate the temp table with the direct reporting employees and have your 2nd list box query that temp table on the form sorted by the ID (Autonum) asc. EG :-
DoCmd.Runsql "INSERT INTO tblTemp EmpID WHERE ReportsToID = " & lst1stListBox.Value

2. Still inside the 'On Click' event, Loop through each of the results in lst2ndListBox and use docmd.runsql command to insert additional employees who report to any of the employees listed something like :-

lst2ndListBox.Requery
tmpFirst=0
tmpLast=lst2ndListBox.ListCount-1
Do
For tmpCounter = tmpFirst to tmpLast
DoCmd.RunSql "INSERT INTO tblTemp EmpID SELECT EmpID WHERE ReportsToID = " & lst2ndListBox.column(1,tmpCounter)
Next tmpCounter
lst2ndListBox.requery
tmpFirst=tmpLast+1
tmpLast=lst2ndListBox.ListCount
Loop until tmpFirst>=tmpLast or tmpFirst>=lst2ndListBox.ListCount

Each pass of the loop should move through each level of reporting.

This should do the job, but may need a bit of tweaking. You have to be sure that your data is ok, ie, that you don't have an employee 'A' who reports to 'B' and 'B' reports to 'A', if this is ever the case, then the above code will loop and flood the temp table.

Hope that Helps

Paul

 

[llkhoutx]

Shifty - a temporary table in not appropriate. With a table of Names and a correcponding pointer to the EmployeeSubEmpoloyee table (technically called a junction table), a simple filtered query provides desired the list.

With a large number of employees, recursion may be excessively time consuming.

 

[john471]

...if I select a company manager I would like to list all Asst. Mgr's reporting into that manager and in addition all supervisors reporting into those AM's, all employee's reporting into those supervisors etc

llkhoutx,

It would seem to me that recursion (with an in-determinate number of iterations) is exactly what Dave's request is, and that a simple filtered query would only provide the first (direct) level of reporting.

I haven't examined ShiftyJaffa's code in any detail, but sureley recursion would be required to produce the list Dave is asking for ???

(Note : I make no comment on the temporary table issue.)

Regards

John.

 

[Dave_cha]

Thanks guys for your help with this.

A recursive lookup would appear to be the solution I'm looking for (Thanks for the code for that Shifty).
I'm can't comment on whether or not the temp table is the most efficient method but it seems a good option for the moment. If there are any alternatives I should also consider please let me know.

Thanks again,

Dave

 

[Dave_cha]

Hi folks,

I've managed to get Shifty's solution up and running however, while it works it's very slow and would not be a viable for this reason.

Does anyone know how I could tweek the code below to speed things up....maybe through the use of an array rather than table?

Thanks,

Dave

Private Sub List0_Click()

DoCmd.Hourglass True
DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE * FROM [tmptbl]"

DoCmd.RunSQL "INSERT INTO [tmptbl] ( empid ) SELECT [Reporting line].EMPLOYEE FROM [Reporting line] WHERE [Manager]= [Forms]![Reporting Lines]![List0];"

Me.List2.Requery

Dim TMPFIRST, TMPLAST, TMPCOUNTER As Integer
TMPFIRST = 0
TMPLAST = Me.List2.ListCount - 1

Do

Me.List2.Requery

For TMPCOUNTER = TMPFIRST To TMPLAST
DoCmd.RunSQL "INSERT INTO [tmptbl] ( empid ) SELECT [Reporting line].EMPLOYEE FROM [Reporting line] WHERE [Manager] = '" & Me.List2.Column(0, TMPCOUNTER) & "';"
Next TMPCOUNTER

TMPFIRST = TMPLAST + 1
TMPLAST = Me.List2.ListCount - 1
Loop Until TMPFIRST >= TMPLAST

DoCmd.SetWarnings True
DoCmd.Hourglass False

End Sub

 

[ShiftyJaffa]

Hi Dave

I did say is was a bit primative . You don't say how many records you have, but if its 1000's then it would have to run a query for each one ... not good.

I'm not sure how well an array would work or what the limits are, so I'll leave that to someone else.

I did have another look at the code and you should be able to reduce the number of queries run to 1 for each level of reporting.

I have'nt tested most of this, but did try the new query which worked by adding a reporting layer each time it was run. You just need to prevent it checking previous layers in the temptable each time it is run, hence the use of the pointers.

Try something like this :-

Private Sub List0_Click()
Dim tmpPointer1, tmpPointer2

DoCmd.Hourglass True
DoCmd.SetWarnings False

' Reset Temp Table
DoCmd.RunSQL "DELETE * FROM [tmptbl]"

' Put 1st reporting level in temp table
DoCmd.RunSQL "INSERT INTO [tmptbl] ( empid ) SELECT [Reporting line].EMPLOYEE FROM [Reporting line] WHERE [Manager]= [Forms]![Reporting Lines]![List0];"

' Update the ListBox and initialise the pointers
Me.List2.Requery
tmpPointer1 = 0
tmpPointer2 = Me.List2.Column (0,Me.List2.ListCount-1)

Do

' Add EMPLOYEE's where their ManagerID field exists in the temp table with an ID value > the last ID number entry in the temp table. (Assumes ID is the autonumber field in your temptable).
DoCmd.RunSQL = "INSERT INTO tmptbl ( empid ) SELECT EMP.EMPLOYEE FROM [Reporting Line] AS EMP WHERE (((Exists (Select empid FROM [Reporting Line] INNER JOIN tmptbl ON [Reporting Line].Manager = tmptbl.empid WHERE empid = EMP.Manager AND ID > " & Me.List2.Column (0,tmpPointer1) & "))=True));"

' Make sure your list box 2 includes the autonumber ID field in column 0 (width 0 hidden and ID ASC in query)

' Requery the List box 2 and move your pointers forward so they cover the new reporting level
Me.List2.Requery
tmpPointer1 = tmpPointer2
tmpPointer2 = Me.List2.Column (0,Me.List2.ListCount-1)

Loop Until tmpPointer1 >= tmpPointer2

DoCmd.SetWarnings True
DoCmd.Hourglass False

End Sub

Regards

Paul