Access 2000 Recursive Query Help

[Spyder]

Hi all,

I know this topic has been beaten to death now but I just don't get it
and could really use some help.

We have a access database that is used for bills of materials (parent
child relationship). There is a table for the Bill's and a table for
the items themself.

So you have a part that consists of subparts which consists of
subsubparts and so on (the depth of the levels is not known).

So, the table that I think I would need to use for the query is the
bill of material table called ITEMBOMS. Its strucutre is simple:

ParentID
ChildID
Qty

Now, here is what I am trying to do.

1. Create a query that shows all the children, sub children ect for
any given parent (travel down the tree).
2. Create a query that shows a childs immediate parent (up one level)
and also finds the uppermost parent (the last parent without).

Query 1 will simply give me a indented bill of material.
Query 2 will tell me how many of any given child is used for any given
parent assembly and the name of the topmost products they are used on.

I hope this makes sense. Essentially I need to create a bill of
material and be able to find out where an item is used within the
bill.

Thanks,

BRian

 

[Michel Walsh]

Hi,


If you have a nested set representation of your hierarchy, that is a
piece of cake.

a.
==================
SELECT a.*
FROM myNestedSet As a
WHERE a.lft BETWEEN givenParentLft AND givenParentRgt
==================

b.
==================
SELECT b.NodeName
FROM myNestedSet AS a, myNestedSet AS b
WHERE givenlft > a.lft AND givenlft < a.rgt
GROUP BY b.NodeName
HAVING MIN(b.lft) > MAX(a.lft) AND MAX(b.lft) <= MIN(a.rgt)
==================


As you know, or observed, a nested set has two fields, lft and rgt, those
are integer that describe the hierarchy. Any node c with c.lft that is
between a.lft and a.rgt is "under" the node a.


(A) -- node a
(A (B) (C) ) -- nodes b and c under A
(A (B (D) ) (C) ) -- node D under node B
(A (B (D) ) (C (E) (F) ) ) -- E and F under C
(A (B (D (G) ) ) (C (E) ( F ) ) ) -- G under D
1 2 3 4 5 6 7 8 9 10 11 12 13 14

The last line is just a running count the parentheses.

the lft value is the number under the ( parenthesis that belongs to the
node:

A.lft=1
G.lft=4


the rgt value is... the number under the ) parenthesis that belongs to the
node:

G.rgt=5
D.rgt=6
F.rgt=12
C.rgt=13
A.rgt=14


Another possibility, each node has at least two positions, and has to be
"under". Example, G is under D, D is under B, and so on, we have:
01 02 03 04 05 06 07 08 09 10 11 12 13 14
A A A A A A A A A A A A A A
B B B B B B C C C C C C
D D D D E E F F
G G

the lft value is the number under the first occurence, the the rgt value,
under the last occurence:

a.lft=1
c.lft=8
c.rgt=13



So from this representation, to get all the nodes under a,

SELECT * FROM myNestedSet WHERE lft BETWEEN 1 AND 14

since a.lft=1 and a.rgt=14


To get all the parent of a node is similar.

SELECT * FROM myNestedSet WHERE myLft BETWEEN lft AND rgt

as example, for node G, we hast G.lft =4, giving

SELECT * FROM myNestedSet WHERE 4 BETWEEN lft AND rgt



Counting these returns the "level" you are. In other word, if the count(*)
returns 1, you are at the top. You can get the immediate parent getting the
MAX lft value among all the nodes on top of you (excluding yourself).


Note that the line

HAVING MIN(b.lft) > MAX(a.lft) AND MAX(b.lft) <= MIN(a.rgt)

can be replaced by

HAVING LAST(b.lft) > MAX(a.lft) AND LAST(b.lft) <= MIN(a.rgt)

or by

HAVING MAX(b.lft) > MAX(a.lft) AND MIN(b.lft) <= MIN(a.rgt)

or any aggregate around b.lft, since that is the same value that is repeated
all over, for a given GROUP, and we just need to aggregate it somehow.




Hoping it may help,
Vanderghast, Access MVP

 

[Spyder]

THanks for the reply.

My problem is that I have no left or right counters in the table
structure. All I have is the ParentID and a ChildID.

So far all I have found refers to Joe Celko's solution using those
left and right counters to identify the nodes of the tree.

Another solution could be to use a Tree Control in Access to display
the results. Can anyone explain how to use this control for a total
newbie?

Thanks all,

Brian