Hierarchy Sorting

[Steve]

Hello All,

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.

If anyone has any suggestions for a better way to do this, thanks in
advance.

Steve

 

[Gary Walter]

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.

 

[sneuendorf]

Hello Gary,

I want to thank you for this. I apologize for taking so long; the project it
is for was postponed, so I did not follow up. But I did not get it to work
either, and I tried a few variations but with no success.

Regarding your assumptions, the root node ID is always the integer 32767 and
shows as the parent for all of the nodes at the first level of the hierarchy.
There is no actual record for an element with that ID.

When I look in the properties for the ID Parent and Position fields in
access, all show as integer.

I appreciate your help.

Thanks,

Steve

 

[Gary Walter]

I want to thank you for this. I apologize for taking so long; the project it
is for was postponed, so I did not follow up. But I did not get it to work
either, and I tried a few variations but with no success.

Regarding your assumptions, the root node ID is always the integer 32767 and
shows as the parent for all of the nodes at the first level of the hierarchy.
There is no actual record for an element with that ID.

When I look in the properties for the ID Parent and Position fields in
access, all show as integer.

Hi Steve,

It sounds like instead of first running qryRoot,
you instead need to just add the root record to tblOrg


ID '0000032767' 'Text(10) pk
Parent Null 'Text(10) (allow Null, i.e., Required = No)
Position 0 'Long
Depth 0 'Long (allow Null)
Lineage '/' 'Text(255) (allow Null)

(remember...no "Default Value" for Position and Depth)

so....
When you want to run your report

'*** aircode***
Dim db As DAO.Database
Dim strSQL as String

Set db = CurrentDb

'clear tblOrg
db.Execute "DELETE * FROM tblOrg", dbFailOnError

'execute your append query "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.
'i.e.,
'Right("0000000000" & yurdata.ID, 10)
'Right("0000000000" & yurdata.Parent, 10)

db.Execute "qryGetData", dbFailOnError

'add root whose ID will be '0000032767' (can this change?)
strSQL="INSERT INTO tblOrg (ID, Parent, Position, Depth,Lineage) " _
& "VALUES ('0000032767',Null,0,0,'/')"

db.Execute strSQL, dbFailOnError

'make passes until all have Depth and Lineage
Do While Dcount("*","tblOrg","[Depth] Is Null") > 0

'execute update query "qryOnePass"
'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));


db.Execute "qryOnePass", dbFailOnError
Loop

db.Close
'*** end aircode***

for example, getdata query produces something like

ID Parent Position Depth Lineage
0000040000 0000032767 1
0000040001 0000032767 2
0000040002 0000032767 3
0000050000 0000040000 1
0000050001 0000040001 2
0000060000 0000050000 1

your insert query produces

ID Parent Position Depth Lineage
0000040000 0000032767 1
0000040001 0000032767 2
0000040002 0000032767 3
0000050000 0000040000 1
0000050001 0000040001 2
0000060000 0000050000 1
0000032767 0 0 /

the first time you run qryOnePass produces

ID Parent Position Depth Lineage
0000040000 0000032767 1 1 /0000032767/
0000040001 0000032767 2 1 /0000032767/
0000040002 0000032767 3 1 /0000032767/
0000050000 0000040000 1
0000050001 0000040001 2
0000060000 0000050000 1
0000032767 0 0 /

second run produces

ID Parent Position Depth Lineage
0000040000 0000032767 1 1 /0000032767/
0000040001 0000032767 2 1 /0000032767/
0000040002 0000032767 3 1 /0000032767/
0000050000 0000040000 1 2 /0000032767/ 0000040000/
0000050001 0000040001 2 2 /0000032767/ 0000040001/
0000060000 0000050000 1
0000032767 0 0 /

third run produces

ID Parent Position Depth Lineage
0000040000 0000032767 1 1 /0000032767/
0000040001 0000032767 2 1 /0000032767/
0000040002 0000032767 3 1 /0000032767/
0000050000 0000040000 1 2 /0000032767/ 0000040000/
0000050001 0000040001 2 2 /0000032767/ 0000040001/
0000060000 0000050000 1 3 /0000032767/ 0000040000/ 0000050000/
0000032767 0 0 /

maybe that's not the kind of data you get from "qryGetData" ?

Good Luck,

gary