Hierarchy Query - Single Table 1

[danneedham]

Hi All

I'm scratching my head a little here! Trying to mimic a function on a financial system CODA. It relates to reporting groups.

I currently have to tables:

Table 1, Node Parent
Node ID (TEXT ID) E.G. TOTSAL1
Node Name (Text) E.G. Total Sales

Table 2, Node Child
ChildID (AutoID) E.G. 1
Element (Text) E.G. 1, 2 or N
ElemFrom (Text) E.G. (Either an element 1, 2 or another node)
ElemTo (Text) E.G. (As above)

Basically - Element 1 and 2 I dont need to do anything with.

If Node Child contains "N" as an element I need to create a query to look through the table and supliment this with the relevant Node within the hierarchy that does contain a element 1 or 2

Eg. Record 1
TOTSAL1 Total Sales
N TOTSAL2
N TOTSAL3

Record 2:
TOTSAL2 Total Own Sales
1 011001 (to) 012999
2 A0000 (to) A9999

Record 3:
TOTSAL3 Concession Sales
1 013000
2 A0000 (to) A9999

The ideal result of the query would be:

TOTSAL1
1 011001 (to) 012999
2 A0000 (to) A9999
1 013000
2 A0000 (to) A9999


Does this make sense?


Thanks in advance for your help.


Dan

 

[PHV]

How is Table 2, Node Child related to Table 1, Node Parent ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[danneedham]

Hi PH

Missed a column, within table two there is a Column with "NodeID".

Hope this helps.


Thanks


Dan

 

[PHV]

A starting point:

SELECT A.NodeID,B.Element,B.ElemFrom,B.ElemTo
FROM tblNodeChild A INNER JOIN tblNodeChild B ON A.ElemFrom = B.NodeID
WHERE A.Element = 'N'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[danneedham]

Hi

That is great, just taking it a little further... Now if i have a child of a child eg. Nested more than one deep?

Also, combining children of the hierarchy with the actual records (ie, the final elements 1s and 2s).

Thanks again for your help.


Dan

 

[danneedham]

Hi Guys

A Hierarchy node can be buried several levels deep.

ie, Node: TOTGP - TOTAL GROSS PROFIT
has several sub nodes
Node: TOTSAL - Total Own Sales
Node: CONSAL - Concession Sales
Node: CONSAL1 - Concession Sales (1)
Node: CONSAL1 - Concession Sales (2)
Node: TOTCOS - Cost Of Sales

How would i integrate this into a query, so for example "TOTGP" would pull though all codes down to a level where there is not a "N" but a 1 or a 2?

With the above example it only goes "1" deep?

SELECT A.NodeID,B.Element,B.ElemFrom,B.ElemTo
FROM tblNodeChild A INNER JOIN tblNodeChild B ON A.ElemFrom = B.NodeID
WHERE A.Element = 'N'

Your help would be greatly appreciated.

Thanks

Dan