Solved

Brain cramp! Sorting records in query involving self-referencing table

Posted on 2012-04-05
14
529 Views
Last Modified: 2012-06-27
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
Comment
Question by:Dale Fye (Access MVP)
  • 6
  • 5
  • 3
14 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37813086
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 37813133
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37813237
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37813271
<but the Act_Abbr fields don't actually look like that. >

why not post the exact data?
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 37813278
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 37813304
@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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37813336
ok..nevermind
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37813372
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37813700
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 0 total points
ID: 37813893
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37823619
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 37823678
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37824594
OK...

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

:-(

Jeff
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 37826801
Didn't know I could grade my own answer
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now