Solved

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

Posted on 2012-04-05
14
600 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 48

Author Comment

by:Dale Fye
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 120

Expert Comment

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

why not post the exact data?
0
 
LVL 48

Author Comment

by:Dale Fye
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 48

Author Comment

by:Dale Fye
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37813336
ok..nevermind
0
 
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 48

Accepted Solution

by:
Dale Fye 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 48

Author Comment

by:Dale Fye
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 48

Author Closing Comment

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

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

632 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