Re: Hierarchy Sorting
- From: "Gary Walter" <gary@xxxxxxxxxxx>
- Date: Mon, 26 Feb 2007 06:25:52 -0600
"Steve" wrote:
I am working with a set of data for which I need to produce a sorted
hierarchy (1.1.1.1.1.1. ...) list. There seems to be no limit to the
number of levels, though I am using 12 levels as a practical limit.
Each record has 3 fields to define its position in the hierarchy: ID,
Parent, Position. The ID is a unique identifier that has no relevance
to that record's position in the hierarchy. The Parent is the ID for
the record that that item is subordinate to and the position is the
order where that item falls relative to all of the other IDs with the
same Parent. All level 1 IDs have a common Parent with a very high ID
number.
What I did, and it works fine, is to create an inquiry that lists the
children of the root node Parent and assign a hierarchy number using
that IDs position with a 0 for all subordinate hierarchy levels. So
the first element would be ID and the position would be
1.0.0.0.0.0.0.0.0.0.0.0. Each subsequent inquiry uses that result to
identify records with those IDs as parents and appends their position
number to their hierarchy, so I would now have a list of
1.1.0.0.0.0.0.0.0.0.0.0, 1.2. . . . and so on through a series of 12
such inquiries. I then join the lists with a union query and sort at
each level, ending up with a sorted list of IDs in their correct
hierarchy order.
My problem is that this approach is slow and messy. I have a pretty
fast machine, but some of the users of this tool will not. I also need
2 instances of the set of queries, since I am doing this to produce a
report of hierarchy position changes for sequential instances of this
data.
I am using Access 2003, but imagine I will need to test and provide it
in earlier versions for some of the users. The data resides in 3rd
party commercial software (paradox based) and I can only read the
source data and cannot add to it or modify it at its source in any
way. The users may want to run it dynamically, that is to iteratively
make changes in the source then view the report, so latency is
important.
I don't know if this is "better," but may worth a shot...
I am going to assume Parent for root ID is null
and ID is type Long so max = 2,147,483,648
If those assumptions are wrong, please ignore the following....
This method was illustrated by Rob Volk:
http://www.sqlteam.com/item.asp?ItemID=8866
create a table (say "tblOrg")
ID Text(10) pk
Parent Text(10) (allow Null, i.e., Required = No)
Position Long
Depth Long (allow Null)
Lineage Text(255) (allow Null)
Create an append query (say "qryGetData")
that gets ID, Parent, and Position from your
3rd party and fills tblOrg, but converts ID and
Parent to "0 justified" text in the process.
Right("0000000000" & yurdata.ID, 10)
Right("0000000000" & yurdata.Parent, 10)
Then create 2 queries:
qryRoot:
UPDATE tblOrg As T
SET
T.Lineage = '/',
T.Depth = 0
WHERE
(((T.Parent) Is Null));
qryOnePass:
UPDATE tblOrg AS T INNER JOIN tblOrg AS P
ON T.Parent = P.ID
SET T.Depth = [P].[Depth]+1,
T.Lineage = [P].[Lineage] & [T].[Parent] & '/'
WHERE
(((P.Depth)>=0)
AND
((P.Lineage) Is Not Null)
AND
((T.Depth) Is Null));
When you want to run your report
'*** aircode***
Dim db As DAO.Database
Set db = CurrentDb
'clear tblOrg
db.Execute "DELETE * FROM tblOrg", dbFailOnError
'execute your append query
db.Execute "qryGetData", dbFailOnError
'set root
db.Execute "qryRoot", dbFailOnError
'make passes until all have Depth and Lineage
Do While Dcount("*","tblOrg","[Depth] Is Null") > 0
db.Execute "qryOnePass", dbFailOnError
Loop
db.Close
'*** end aircode***
You then just sort by Lineage and Position in your report.
The Text(10) should allow you to go to a depth
of at least 22.
I don't know if this will be faster but it has worked for
me in similar situation as the fastest alternative.
.
- References:
- Hierarchy Sorting
- From: Steve
- Hierarchy Sorting
- Prev by Date: Re: QBF Query by Form- more than 2 tables?
- Next by Date: Re: Subqueries
- Previous by thread: Hierarchy Sorting
- Next by thread: RE: Increment Sequence Count Within a Query
- Index(es):
Relevant Pages
|
