Solved

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

Posted on 2012-04-05
14
524 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 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

13 Experts available now in Live!

Get 1:1 Help Now