Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

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:expected resultsSort-Activites.mdb
0
Dale Fye
Asked:
Dale Fye
  • 6
  • 5
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this query

SELECT temp_Act_Treeview.Act_ID, temp_Act_Treeview.Act_FY, temp_Act_Treeview.Act_Abbr, temp_Act_Treeview.Act_Start, temp_Act_Treeview.Parent_ID
FROM temp_Act_Treeview
ORDER BY Mid([Act_Abbr],InStrRev([Act_Abbr],"Event")), temp_Act_Treeview.Act_Abbr;
0
 
Dale FyeAuthor Commented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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, temp_Act_Treeview.Act_Start, temp_Act_Treeview.Parent_ID, CLng(Right([Act_Abbr],1)) AS SortBy
FROM temp_Act_Treeview
ORDER BY CLng(Right([Act_Abbr],1));
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rey Obrero (Capricorn1)Commented:
<but the Act_Abbr fields don't actually look like that. >

why not post the exact data?
0
 
Dale FyeAuthor Commented:
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
0
 
Dale FyeAuthor Commented:
@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.
0
 
Rey Obrero (Capricorn1)Commented:
ok..nevermind
0
 
Jeffrey CoachmanMIS LiasonCommented:
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, temp_Act_Treeview.Act_Start, temp_Act_Treeview.Parent_ID, CLng(Right([Act_Abbr],1)) AS SortBy, IIf(Len([Act_Abbr])<10,0,1) AS IsSub, IIf((IIf(Len([Act_Abbr])<10,0,1))=True,CLng(Mid([Act_Abbr],12,1)),"") AS SubNum1, IIf((IIf(Len([Act_Abbr])<10,0,1))=True,CLng(Mid([Act_Abbr],24,1)),"") AS SubNum2
FROM temp_Act_Treeview
ORDER BY CLng(Right([Act_Abbr],1)), IIf(Len([Act_Abbr])<10,0,1), IIf((IIf(Len([Act_Abbr])<10,0,1))=True,CLng(Mid([Act_Abbr],12,1)),""), IIf((IIf(Len([Act_Abbr])<10,0,1))=True,CLng(Mid([Act_Abbr],24,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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Dale FyeAuthor Commented:
Gentlemen,

Thanks for your efforts

Based on harfang's article on Managing Trees and Heirarchy's, I came up with a solution.  See attached database.

Basically, this uses a Fast Table Lookup Function (another harfang article) to generate a unique string that I can use in a sort by.  Once I figured that out, I was able to construct a SQL string that accomplishes the same thing (below).

SELECT Activity.Act_ID,
            Activity.Act_FY,
            Activity.Act_Abbr,
            Activity.Act_Start,
            IIf(IsNull([Activity.Parent_ID]),Null,Format([Parent].[Act_Start],"yy/mm/dd")+"("+Format([Activity].[Parent_ID],"000000")+") -  ") & Format([Activity].[Act_Start],"yy/mm/dd") AS Path
FROM temp_Act_Treeview AS Activity
LEFT JOIN temp_Act_Treeview AS Parent
ON Activity.Parent_ID = Parent.Act_ID
ORDER BY IIf(IsNull([Activity.Parent_ID]),Null,Format([Parent].[Act_Start],"yy/mm/dd")+"("+Format([Activity].[Parent_ID],"000000")+") -  ") & Format([Activity].[Act_Start],"yy/mm/dd");Sort-Activites.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Dale FyeAuthor Commented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK...

Great,  but I am sorry I could not have helped more...

:-(

Jeff
0
 
Dale FyeAuthor Commented:
Didn't know I could grade my own answer
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now