Brain cramp! Sorting records in query involving self-referencing table
I'm having a brain cramp this afternoon.
I have a treeview on one of my forms which is currently taking forever to load (this may be a server issue but I'm currently unable to verify this). Currently the code develops an initial recordset based on the Act_FY (fiscal year) field in the attached table. As it loops through that recordset, it creates the parent node, then calls creates another recordset which based on the same table but with filters Act_FY = rs!Act_FY and Parent_ID is nULL, sorted by the Act_Start (date) field. As it loops through this recordset, it creates the activity Node, and then checks for sub-activities, Parent_ID = rs2!Parent_ID. This all works, but is slow.
What I would like to do is create a single query, that returns all of these records in a single recordset, sorted by Act_Start, but with the caveat that if an activity has a parent, it should first be sorted by the parent Act_Start.
It is possible that a parent activity could either precede or follow one of it's child activities, although the child activities will generally precede the parent. If there are two activities (which do not have a parent) that start on the same day, there is no specific priority of the order they should appear in.
I've attached a sample database with the table that I'm working with and 10 records. Given these 10 records, I would like the query result to look like:
Sort-Activites.mdb
I have a treeview on one of my forms which is currently taking forever to load (this may be a server issue but I'm currently unable to verify this). Currently the code develops an initial recordset based on the Act_FY (fiscal year) field in the attached table. As it loops through that recordset, it creates the parent node, then calls creates another recordset which based on the same table but with filters Act_FY = rs!Act_FY and Parent_ID is nULL, sorted by the Act_Start (date) field. As it loops through this recordset, it creates the activity Node, and then checks for sub-activities, Parent_ID = rs2!Parent_ID. This all works, but is slow.
What I would like to do is create a single query, that returns all of these records in a single recordset, sorted by Act_Start, but with the caveat that if an activity has a parent, it should first be sorted by the parent Act_Start.
It is possible that a parent activity could either precede or follow one of it's child activities, although the child activities will generally precede the parent. If there are two activities (which do not have a parent) that start on the same day, there is no specific priority of the order they should appear in.
I've attached a sample database with the table that I'm working with and 10 records. Given these 10 records, I would like the query result to look like:
Sort-Activites.mdb
ASKER
Ray,
That works great for this particular dataset, but the Act_Abbr fields don't actually look like that. They are names/titles of events which have no real bearing on the sort order.
This is going to be about the Act_Start date field.
Dale
That works great for this particular dataset, but the Act_Abbr fields don't actually look like that. They are names/titles of events which have no real bearing on the sort order.
This is going to be about the Act_Start date field.
Dale
Try something like this as a start.
(Not perfect, but I am sure you see where I am going with the logic)
SELECT temp_Act_Treeview.Act_ID, temp_Act_Treeview.Act_FY, temp_Act_Treeview.Act_Abbr
FROM temp_Act_Treeview
ORDER BY CLng(Right([Act_Abbr],1));
(Not perfect, but I am sure you see where I am going with the logic)
SELECT temp_Act_Treeview.Act_ID, temp_Act_Treeview.Act_FY, temp_Act_Treeview.Act_Abbr
FROM temp_Act_Treeview
ORDER BY CLng(Right([Act_Abbr],1));
<but the Act_Abbr fields don't actually look like that. >
why not post the exact data?
why not post the exact data?
ASKER
Jeff,
as I mentioned to Ray, the Act_Abbr I posted was just an example. The actual values in that field have no relevance to the example. Probably a bad decision on my part.
I'm reading an article posted by harfang to see if it will answer my problem. Will post back with a solution if I find one.
Dale
as I mentioned to Ray, the Act_Abbr I posted was just an example. The actual values in that field have no relevance to the example. Probably a bad decision on my part.
I'm reading an article posted by harfang to see if it will answer my problem. Will post back with a solution if I find one.
Dale
ASKER
@capricorn1
<why not post the exact data?>
The actual values contained corporate names that I would rather not divulge. Besides, I thought it was immaterial, as my description of the problem clearly indicated that this is about ordering by the start date (Act_Start). Apparently this was not as clear as I thought, because Jeff had a similar idea using Act_Abbr.
<why not post the exact data?>
The actual values contained corporate names that I would rather not divulge. Besides, I thought it was immaterial, as my description of the problem clearly indicated that this is about ordering by the start date (Act_Start). Apparently this was not as clear as I thought, because Jeff had a similar idea using Act_Abbr.
ok..nevermind
fyed, Then sorry, I too misunderstood...
In any event, ...it was a good exercise any way
;-)
SELECT temp_Act_Treeview.Act_ID, temp_Act_Treeview.Act_FY, temp_Act_Treeview.Act_Abbr
FROM temp_Act_Treeview
ORDER BY CLng(Right([Act_Abbr],1)),
presumes:
1. Set lengths for all values in the Act_Accr field
2. 0-9 values only
...and probably some other stuff as well
...perhaps something can be gleaned from this...
Jeff
In any event, ...it was a good exercise any way
;-)
SELECT temp_Act_Treeview.Act_ID, temp_Act_Treeview.Act_FY, temp_Act_Treeview.Act_Abbr
FROM temp_Act_Treeview
ORDER BY CLng(Right([Act_Abbr],1)),
presumes:
1. Set lengths for all values in the Act_Accr field
2. 0-9 values only
...and probably some other stuff as well
...perhaps something can be gleaned from this...
Jeff
Even without the real data, it would seem that you could adapt something like that to work...
Again, I know I made a lot of presumptions, ...and I do have a habbit of taking a bit of a "Hammer" view of some problems..
;-)
But the tree view basically does something like this, correct?
1
2
3
3.1
3.2
4
4.1
5
5.1
My logic was that if you could pull out all the "Root" numbers, then pull out all the "Subs", then pull out the two number in the sub, ...then you could sort by the Root numeric fields, then sort by the two sequential "Sub" numbers...
Again, perhaps this logic can be applied to the actual Tree data...?
;-)
Jeff
Again, I know I made a lot of presumptions, ...and I do have a habbit of taking a bit of a "Hammer" view of some problems..
;-)
But the tree view basically does something like this, correct?
1
2
3
3.1
3.2
4
4.1
5
5.1
My logic was that if you could pull out all the "Root" numbers, then pull out all the "Subs", then pull out the two number in the sub, ...then you could sort by the Root numeric fields, then sort by the two sequential "Sub" numbers...
Again, perhaps this logic can be applied to the actual Tree data...?
;-)
Jeff
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hey Dale...
Just so we are straight...
Are you saying that my technique does not give the desired results?
I am not arguing for any points, I just want to know the basic difference between your solution and what I proposed...
Again, my approach was a bit "Brutish", but it appeared to work.
If your solution is :"more portable or Elegant, or is just Faster, this is what I am wanting to know...
;-)
Jeff
Just so we are straight...
Are you saying that my technique does not give the desired results?
I am not arguing for any points, I just want to know the basic difference between your solution and what I proposed...
Again, my approach was a bit "Brutish", but it appeared to work.
If your solution is :"more portable or Elegant, or is just Faster, this is what I am wanting to know...
;-)
Jeff
ASKER
Jeff,
Both your technique and Ray's would probably have worked if the data in the Act_Abbr column was actually formatted as I had indicated. But that was sample data, and the actual data (proprietary) in that column looks nothing like that text (bad decision on my part). It was the date criteria that was critical to the sorting, not the text in the Act_Abbr field.
The neat thing about the solution I ended up using (see the attachment to my solution post) was that it used a Fast Lookup to build a single string that I was able to sort on. This eliminated the need to use a recursive algorithm to build the treeview, so not only was the sort extremely effective, the code that builds the tree runs about 6 times faster than the previous method.
Dale
Both your technique and Ray's would probably have worked if the data in the Act_Abbr column was actually formatted as I had indicated. But that was sample data, and the actual data (proprietary) in that column looks nothing like that text (bad decision on my part). It was the date criteria that was critical to the sorting, not the text in the Act_Abbr field.
The neat thing about the solution I ended up using (see the attachment to my solution post) was that it used a Fast Lookup to build a single string that I was able to sort on. This eliminated the need to use a recursive algorithm to build the treeview, so not only was the sort extremely effective, the code that builds the tree runs about 6 times faster than the previous method.
Dale
OK...
Great, but I am sorry I could not have helped more...
:-(
Jeff
Great, but I am sorry I could not have helped more...
:-(
Jeff
ASKER
Didn't know I could grade my own answer
SELECT temp_Act_Treeview.Act_ID, temp_Act_Treeview.Act_FY, temp_Act_Treeview.Act_Abbr
FROM temp_Act_Treeview
ORDER BY Mid([Act_Abbr],InStrRev([A