Beagle said:
Hello,
Below is what I have:
Level Parent Child
1 A AA
1 A BB
1 A CC
1 B DD
1 B EE
2 AA AAA
2 AA BBB
2 AA CCC
3 AAA AAAA
4 AAAA AAAA
Below is the desired result (flat file format):
Lvl1 Lvl2 Lvl3 Lvl4
A AA AAA AAAA
A BB BBB
A CC CCC
B DD
B EE
I was thinking of Using a Crosstab query but run into because I'm not
summing anything.
Please help.
Beagle
I modified your data to:
Level Parent Child
1 A AA
1 A BB
1 A CC
1 B DD
1 B EE
2 AA AAA
2 BB BBB
2 CC CCC
3 AAA AAAA
4 AAAA AAAAA
Desired result:
Lvl1 Lvl2 Lvl3 Lvl4
A AA AAA AAAA
A BB BBB
A CC CCC
B DD
B EE
You might not need a crosstab query depending on your needs.
Assuming the tree has only one path to each leaf record, it looks like
you want a list of leaves along with their corresponding paths.
To get the list of leaves, the condition is that it shows up in the
Child column, but doesn't show up in the Parent column.
SELECT Child FROM tblTree WHERE Not Exists (SELECT A.Parent FROM tblTree
AS A WHERE A.Parent = tblTree.Child);
After that it got a little ugly since Jet SQL doesn't have much built-in
functionality for dealing with trees:
qryFiveLevels:
SELECT tblTree.Level + 1 AS ChildLevel, Child, Parent, IIf([ChildLevel]
2, (SELECT A.Parent FROM tblTree AS A WHERE A.Child =
tblTree.Parent), '') As P2, IIf([ChildLevel] > 3, (SELECT A.Parent FROM
tblTree AS A WHERE A.Child = IIf(tblTree.Level + 1 > 2, (SELECT A.Parent
FROM tblTree AS A WHERE A.Child = tblTree.Parent), '')), '') As P3,
IIf(tblTree.Level + 1 > 4, (SELECT A.Parent FROM tblTree AS A WHERE
A.Child = IIf(tblTree.Level + 1 > 3, (SELECT A.Parent FROM tblTree AS A
WHERE A.Child = IIf(tblTree.Level + 1 > 2, (SELECT A.Parent FROM tblTree
AS A WHERE A.Child = tblTree.Parent), '')), '')), '') As P4 FROM tblTree
WHERE Not Exists (SELECT A.Parent FROM tblTree AS A WHERE A.Parent =
tblTree.Child);
!qryFiveLevels:
ChildLevel Child Parent P2 P3 P4
2 DD B NullString NullString NullString
2 EE B NullString NullString NullString
3 BBB BB A NullString NullString
3 CCC CC A NullString NullString
5 AAAAA AAAA AAA AA A
That result is backwards from what you want.
qryByLevel:
SELECT Switch(ChildLevel = 2, Parent, ChildLevel = 3, P2, ChildLevel =
4, P3, ChildLevel = 5, P4) AS Level1, Switch(ChildLevel = 2, Child,
ChildLevel = 3, Parent, ChildLevel = 4, P2, ChildLevel = 5, P3) AS
Level2, Switch(ChildLevel = 2, '', ChildLevel = 3, Child, ChildLevel =
4, Parent, ChildLevel = 5, P2) AS Level3, Switch(ChildLevel = 2, '',
ChildLevel = 3, '', ChildLevel = 4, Child, ChildLevel = 5, Parent) AS
Level4, Switch(ChildLevel = 2, '', ChildLevel = 3, '', ChildLevel = 4,
'', ChildLevel = 5, Child) AS Level5 FROM qryFiveLevels;
!qryByLevel:
Level1 Level2 Level3 Level4 Level5
B DD NullString NullString NullString
B EE NullString NullString NullString
A BB BBB NullString NullString
A CC CCC NullString NullString
A AA AAA AAAA AAAAA
Note that it is about the same amount of work to have the queries use
Null's instead of NullString's. Maybe what I've shown will help. Maybe
it won't.
James A. Fortune
(e-mail address removed)
