Need help sorting a self join query

[Max Moor]

Hi All,

I have a table of employees, each with a unique ID. Each employee
record also includes a "ReportsTo" field, which contains either a 1, if
unassigned (a top dog), or the employee ID of another employee in the
table.

In my case, there are never more than three levels of reporting. For
example, Ed may have three people reporting to him, and each of them have 3
people reporting to them. That's as deep as it goes, though. The third
level people never have folks reporting to them.

So, say I have records like below...

ID Name ReportsTo

1 Unassigned (dummy record)
2 Ed 1 (unassigned, so top dog)
3 Sue 2 (reports to Ed)
4 Dave 3 (reports to Sue, then Ed)
5 Bob 3 (reports to Sue, then Ed)
6 Robin 2 (reports to Ed)
7 Roy 6 (reports to Robin, then Ed)

What I want to do is generate a query that will sort the records in a
top-down fashion, with sub-levels in alphabetical order, and use that to
fill a listview. The desired order is:

Ed
Robin
Roy
Sue
Bob
Dave

I put together a self join query to experiment with. There are two
copies of the employee table (the second aliased). I get the data:

ID Name ReportsTo SupersSuper
1 Unassigned 1 1
2 Ed 1 1
3 Sue 2 1
4 Bob 3 2
5 Dave 3 2
6 Robin 2 1
7 Roy 6 2

I just don't see how I can use this information to get the sort I
want. I briefly started considering ugly IIf() statements to calculate
level depth and such, but it started getting complicated really fast.

This sort of nut has to have been cracked before. Can someone show me
the easy - or at least easier - way?

Thanks,
Max

 

[Dale Fye]

You can do this in a query, but it is difficult, and getting the sorting
right is even more complicated. It helps that you only have 3 levels, but I
think I would bypass the query and create a recursive function to load your
list. I'll assume that you want to do this when you load the form, so I
would start out by putting some code in the Open event of the form. This is
untested, and I'm in a hurry, but try something like this:

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
Dim rs As DAO.Recordset

Me.lst_People.RowSource = ""

Call LoadPeopleList(1)
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

End Sub

Private Sub LoadPeopleList(lngID As Long, _
Optional intLevel As Integer = 0)

Dim strSQL As String
Dim rs As DAO.Database

strSQL = "SELECT [ID], [Name] " _
& "FROM yourTable " _
& "WHERE [ReportsTo] = " & lngID _
& " ORDER BY [ID]"
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

While Not rs.EOF
Me.lst_People.AddItem String(intLevel, " ") & rs("Name")
Call LoadPeopleList(rs("ID"), intLevel + 1)
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

End Sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

I was definately in too big a hurry this morning. Way too many errors in
that code. Instead of my earlier solution, try this. You will need to make
sure that the RowSourceType on the list is ValueList, and that it will accept
two columns. Additionally, it appears that the list strips leading spaces,
so I have used hyphens to indent the names.

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
Dim rs As DAO.Recordset

Me.lst_People.RowSource = ""

Call LoadPeopleList(1)

End Sub

Private Sub LoadPeopleList(lngID As Long, _
Optional intLevel As Integer = 0)

Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT [ID], [Name] " _
& "FROM yourTable " _
& "WHERE [ReportsTo] = " & lngID _
& " ORDER BY [ID]"
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

While Not rs.EOF
Me.lst_People.AddItem rs("ID") & ";" & String(intLevel * 2, "-") &
rs("Name")
Call LoadPeopleList(rs("ID"), intLevel + 1)
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

End Sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Max Moor]

I was definately in too big a hurry this morning. Way too many errors
in that code. Instead of my earlier solution, try this. You will need
to make sure that the RowSourceType on the list is ValueList, and that
it will accept two columns. Additionally, it appears that the list
strips leading spaces, so I have used hyphens to indent the names.

Hi Dale,

I've never thought of using a recursive function in VBA. I'm an old C
guy, and did it in that language. I'll have to play with this a bit, and get
my head around it, but it seems a very elegant solution.

Thanks!
Max

 

[John W. Vinson]

Hi Dale,

I've never thought of using a recursive function in VBA. I'm an old C
guy, and did it in that language. I'll have to play with this a bit, and get
my head around it, but it seems a very elegant solution.

Recursion, n. See: Recursion.

- The Geek's Dictionary