RE: Need help sorting a self join query
- From: Dale Fye <dale.fye@xxxxxxxxxx>
- Date: Wed, 22 Oct 2008 05:23:01 -0700
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.
"Max Moor" wrote:
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
- References:
- Need help sorting a self join query
- From: Max Moor
- Need help sorting a self join query
- Prev by Date: Re: How to Write an SQL query to
- Next by Date: Re: Manipulating data type from linked table to run query
- Previous by thread: Need help sorting a self join query
- Next by thread: RE: Need help sorting a self join query
- Index(es):
Relevant Pages
|
