Sorting hell

[Phil Hellmuth]

I've got a table with two relevant columns: Code and ParentCode. 'Code'
is a child of the parent. A ParentCode value will also exist as the
child code in another row in the table. For the highest row in the
tree, the Code and ParentCode will have the same value. There's a
limitless number of iterations in the hierarchy of parent/child codes.
For example, we may have something like this:

Code ParentCode
---- ----------
0000 0000
1000 0000
1500 1000
2000 1000
2500 1000
3000 2000

I need to present the data, either via query or creating a new table via
VBA, so that it's sorted by the hierarchy, for as many levels as it may
go. Using the above data as an example, I want to present the data in
the following way:

Code ParentCode
---- ----------
0000 0000
1000 0000
1500 1000
2000 1000
3000 2000
2500 1000

3000/2000 moves up a notch, because I want to keep next sequentially
because of the 2000 value.

As I said, the number of branches in the tree are limitless, which
really complicates the matter. Is there a simple way to do this?

Thanks in advance.

 

[Gary Walter]

"Phil Hellmuth"wrote:

I've got a table with two relevant columns: Code and ParentCode. 'Code'
is a child of the parent. A ParentCode value will also exist as the child
code in another row in the table. For the highest row in the tree, the
Code and ParentCode will have the same value. There's a limitless number
of iterations in the hierarchy of parent/child codes. For example, we may
have something like this:

Code ParentCode
---- ----------
0000 0000
1000 0000
1500 1000
2000 1000
2500 1000
3000 2000

I need to present the data, either via query or creating a new table via
VBA, so that it's sorted by the hierarchy, for as many levels as it may
go. Using the above data as an example, I want to present the data in the
following way:

Code ParentCode
---- ----------
0000 0000
1000 0000
1500 1000
2000 1000
3000 2000
2500 1000

3000/2000 moves up a notch, because I want to keep next sequentially
because of the 2000 value.

As I said, the number of branches in the tree are limitless, which really
complicates the matter. Is there a simple way to do this?

No Phil, there is no "simple way."

There is a "straight-forward way" though...
(I did not test below so could be wrong)

One method for this was illustrated by Rob Volk:
http://www.sqlteam.com/item.asp?ItemID=8866

create a table (say "tblOrg")

Code Text(10) pk
ParentCode Text(10)
Depth Long (allow Null)
Lineage Text(255) (allow Null)

Create an append query (say "qryGetData")
that gets Code and ParentCode from your
table and fills tblOrg, but converts Code and
ParentCode to "0 justified" text in the process.

qryGetData:
(change "yurtable" to name of your table)

INSERT INTO tblOrg ( Code, ParentCode )
SELECT
Format(

,"0000000000") AS Child,
Format([ParentCode],"0000000000") AS Parent
FROM yurtable;

tblOrg should then look like:

Code               ParentCode      Depth   Lineage
0000000000   0000000000
0000001000   0000000000
0000001500   0000001000
0000002000   0000001000
0000002500   0000001000
0000003000   0000002000

Then create 2 queries:

qryStartRoot:

INSERT INTO tblOrg ( Depth,Lineage)
VALUES (0,'/')
WHERE
[Code] = "0000000000"
AND
[ParentCode] = "0000000000";


tblOrg should then look like:

Code               ParentCode      Depth   Lineage
0000000000   0000000000       0          /
0000001000   0000000000
0000001500   0000001000
0000002000   0000001000
0000002500   0000001000
0000003000   0000002000


qryOnePass:

UPDATE tblOrg AS T INNER JOIN tblOrg AS P
ON T.ParentCode = P.Code
SET T.Depth = [P].[Depth]+1,
T.Lineage = [P].[Lineage] & [T].[ParentCode] & '/'
WHERE
(((P.Depth)>=0)
AND
((P.Lineage) Is Not Null)
AND
((T.Depth) Is Null));

he first time you run it, it will update 1 row:

Code               ParentCode      Depth   Lineage
0000000000   0000000000       0        /
0000001000   0000000000       1        /0000000000/
0000001500   0000001000
0000002000   0000001000
0000002500   0000001000
0000003000   0000002000


the next time you run it, it will update 3 more rows:

Code               ParentCode      Depth   Lineage
0000000000   0000000000       0        /
0000001000   0000000000       1        /0000000000/
0000001500   0000001000       2        /0000000000/0000001000/
0000002000   0000001000       2        /0000000000/0000001000/
0000002500   0000001000       2        /0000000000/0000001000/
0000003000   0000002000

if you continue to run query until no more rows
are updated...

Code               ParentCode      Depth   Lineage
0000000000   0000000000       0        /
0000001000   0000000000       1        /0000000000/
0000001500   0000001000       2        /0000000000/0000001000/
0000002000   0000001000       2        /0000000000/0000001000/
0000002500   0000001000       2        /0000000000/0000001000/
0000003000   0000002000       3        /0000000000/0000001000/0000002000/

So...one example query to see if works for you:

qryIndentedList ("SortField" for demo only):

SELECT
Space([T].[Depth]*4) & Right([Code], 4) As IndentCode,
[Lineage] & [Code] As SortField
FROM tblOrg AS T
ORDER BY
[Lineage] & [Code];

gives:

IndentCode  SortField
0000             /0000000000
1000          /0000000000/0000001000
1500       /0000000000/0000001000/0000001500
2000       /0000000000/0000001000/0000002000
3000  /0000000000/0000001000/0000002000/0000003000
2500       /0000000000/0000001000/0000002500


/////////////////////////////////////////////////

When you want to run a 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

'add root
db.Execute "qryStartRoot", dbFailOnError

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

db.Close

'then open report sorted by Depth and/or Lineage/Code.
'if you need more fields from table for report
'recordsource, join tblOrgs to "yurtable"

'*** end aircode***

The Text(10) should allow you to go to a nested depth
of at least 22. It could be your situation does not
need to go that deep -- so you could reduce "padding."

Like I said, it is non-trivial, but I have used
successfully on several ocassions...

good luck,

 

[Gary Walter]

The Text(10) should allow you to go to a nested depth
of at least 22. It could be your situation does not
need to go that deep -- so you could reduce "padding."

Actually...if all data were 4-digit text,
you might just skip the padding
to go to depth of ~60 or so with the
255 char Lineage (if that is deep enough?).

good luck,

gary