Hierarchy summing

[dskysmine]

Hello guys,
I'm sort of new to Access, but I have been using excel for a long time and this would be so easy with SUMIF... >_>
I have a Tree Hierarchy in which I only have the Values for the "youngest" nodes. What I need it to "Sum up" these Values to the parents Nodes.
I tryed using a Cross tab query with a "Where NODEID=PARENTID Clause" but it results in an empty table. (Doh...)
The Table is something like:
NODEID | PARENTID | Value
1 | 0 | NULL
2 | 1 | NULL
3 | 1 | 2
4 | 2 | 5
5 | 2 | 10
(Of course the real data has over 60k rows... this is just an example)
The thing is, I need to sum up the nodes to see how much "Value" the higher nodes have.
In the example above Node 2 would have 5+10=15, Node 1 would have 2+5+10.
Is there any way to do this in Access or do I have to pass it to excel and use SUMIFS?
The data is very big and would really help being able to do this in Access.
Thank you so much.

[June7]

Try:

Query1
SELECT Table1.ParentID, Sum(Nz([Value],0)) AS SV
FROM Table1
GROUP BY Table1.ParentID;

Query2
SELECT Query1.ParentID, Query1.SV, Sum(q.SV) As RunningValue
FROM Query1, Query1 As q
WHERE Query1.ParentID<=q.ParentID
GROUP BY Query1.ParentID, Query1.SV
ORDER BY Query1.ParentID DESC;

[dskysmine]

Try:

Query1
SELECT Table1.ParentID, Sum(Nz([Value],0)) AS SV
FROM Table1
GROUP BY Table1.ParentID;

Query2
SELECT Query1.ParentID, Query1.SV, Sum(q.SV) As RunningValue
FROM Query1, Query1 As q
WHERE Query1.ParentID<=q.ParentID
GROUP BY Query1.ParentID, Query1.SV
ORDER BY Query1.ParentID DESC;

First I apologize for not replying sooner. I couldn't access the internet during the weekend. I tested with the 3 level hierarchy and it works perfectly. I will try it tomorrow in the Huge database with the 20 levels because today at work I was super busy but at a first glance it seems to be exactly what I needed.
Thank you so much!

I will try to give you a more detailed answer asap.
You really are very bright!
Dan

[dskysmine]

Try:

Query1
SELECT Table1.ParentID, Sum(Nz([Value],0)) AS SV
FROM Table1
GROUP BY Table1.ParentID;

Query2
SELECT Query1.ParentID, Query1.SV, Sum(q.SV) As RunningValue
FROM Query1, Query1 As q
WHERE Query1.ParentID<=q.ParentID
GROUP BY Query1.ParentID, Query1.SV
ORDER BY Query1.ParentID DESC;

Hello June7,
It was almost too good to be true.
As I said in the beggining the Hierarchy is a little more complex. I created a Table with some more values to simulate the fact that some Nodes are inserted into the Hierarchy later and hence have an "older" ParentID, because I noticed that your query organizes the parents by ID in a sequential order and so it appears that for Node 2 the result should be 24 and it appears 32.
Here is the detail (I cannot put attachments).

NODEID	PARENTID	DATA_VALUE
1	0
2	1
3	1	2
4	2	5
5	2	10
6	4	9
7	1	3
8	3	8

Do you think it is possible to make this through queries or does it have to be through VBA?
Thanks.

[June7]

Okay, I am confused and obviously did not fully understand your requirements. From the original post:

The Table is something like:
NODEID | PARENTID | Value
1 | 0 | NULL
2 | 1 | NULL
3 | 1 | 2
4 | 2 | 5
5 | 2 | 10
(Of course the real data has over 60k rows... this is just an example)
The thing is, I need to sum up the nodes to see how much "Value" the higher nodes have.
In the example above Node 2 would have 5+10=15, Node 1 would have 2+5+10.

Why is Node 2 5+10=15 and not 2+5+10=17? I focused my query on the PARENTID because of the 5+10 expression.

From your last post: "appears that for Node 2 the result should be 24"
How do you arrive at 24?

[dskysmine]

Okay, I am confused and obviously did not fully understand your requirements. From the original post:Why is Node 2 5+10=15 and not 2+5+10=17? I focused my query on the PARENTID because of the 5+10 expression.

That example was because NodeID 2 has only 2 "children nodes", node 4 and node 5. If value of node 4 is 5 and value for node 5 is 10 then node 2 has 5+10.
The value 2 is for node 3 which is a "brother" of node 2 (meaning they are on the same level).

From your last post: "appears that for Node 2 the result should be 24"
How do you arrive at 24?

This is for the second example table I posted.
In that example node 2 has still 2 children nodes(node 4 with value 5 and node5 with value 10) but it also has a "granchild" that I added which is node 6 (note that node 6 has parent 4) that has value of 9.
Node 2 will then have 5+10+9=24
If it helps my original hierarchy also has "level" of the node.
For my last example with 8 nodes it would be:

NODEID	PARENTID	DATA_VALUE	Level
1	0		1
2	1		2
3	1	2	2
4	2	5	3
5	2	10	3
6	4	9	4
7	1	3	2
8	3	8	3

[June7]

The Level does seem relevant to the issue. So node ParentID 3 would not be included with node ParentID 2 even though they are the same level?

[dskysmine]

The Level does seem relevant to the issue. So node ParentID 3 would not be included with node ParentID 2 even though they are the same level?

Hi June7,
As I mentioned in the beggining, this is a hierarchy tree. As you know, in a hierarchy tree nodes at the same level do not "sum" in themselves. They sum to their appropriate parentID. You have to visualize the tree that the table represents to understand the problem. Maybe a little drawing would help?
......|-----1------|---------|
......|.................|............ |
|---2---|............7.............3
|...........|...........................|
4..........5............................|
|........................................8
|
6

So 8 sums to 3 and to 1
7 sums to 1
6 sums to 4, to 2 and to 1
5 sums to 2 and to 1.
Is it more clear now?

[June7]

I understand hierarachy and trees (family, organization, directory) but never done anything like calculating a hierarchy tree so this is new to me. The results for the sample data would be:

NodeID	SumValue
1	37
2	24
3	8
4	9
5	0
6	0
7	0
8	0

Sorry, don't see any way to do with queries/subqueries. This requires following various paths and aggregating values along each path. Access can do aggregate calcs as already shown, but don't see anyway to keep node 8 value from adding into the node 2 sum. Might be able to code this in VBA. Probably very complicated.

You have an Excel already doing this? Want to provide it for analysis?

[dskysmine]

I understand hierarachy and trees (family, organization, directory) but never done anything like calculating a hierarchy tree so this is new to me. The results for the sample data would be:

NodeID	SumValue
1	37
2	24
3	8
4	9
5
6
7
8

Sorry, don't see any way to do with queries/subqueries. This requires following various paths and aggregating values along each path. Access can do aggregate calcs as already shown, but don't see anyway to keep node 8 value from adding into the node 2 sum. Might be able to code this in VBA. Probably very complicated.

You have an Excel already doing this? Want to provide it for analysis?

Hello June7,

I have got a better idea.
I made a possible solution using queries and VBA.
First a parametrized make_query to calculate for each level the data to send "upwards" and save it in a new table (temporary)
SELECT T_example.PARENTID, Sum(Table.DATA_VALUE) AS SumOfDATA_VALE INTO T_Results
FROM T_example
WHERE (((T_example.LEVEL)=[Child level]))
GROUP BY T_example.PARENTID;

Then a query to update the above level
UPDATE T_example INNER JOIN T_Results ON T_example.NODEID = T_Results.PARENTID SET T_example.DATA_VALUE = [T_Results].[SumOfDATA_VALUE];

Then I made the code to make the recursive calculation...
Sub Calculations()

Dim db As DAO.Database
Dim qryPar As DAO.QueryDef
Dim levels As Long
levels = DMax("LEVEL", "T_example")
Set db = CurrentDb()

DoCmd.SetWarnings False
While levels > 1

Set qryPar = db.QueryDefs("q1")
qryPar.Parameters("Child level") = levels
qryPar.Execute
DoCmd.OpenQuery ("q2")
levels = levels - 1
DoCmd.RunSQL "DROP TABLE T_Results"
Wend
DoCmd.SetWarnings True

End Sub

It works if the parents don't have values.
But since they do have values I need your help to make in the update table instead of making "data_value = new value" to be something like "data_value = data_value + new value" (if data_value is not null) or "data_value = new value" (if data_value is null)
I tried using
UPDATE T_example INNER JOIN T_Results ON T_example.NODEID = T_Results.PARENTID SET T_example.DATA_VALUE = [T_example].[Data_Value]+[T_Results].[SumOfDATA_VALUE];

but it didn't work.
Do you know of any way to do it?
Thanks!

[June7]

Can't do "data_value = data_value + new value" in a query.

Instead of make table, VBA code would have to do the above calc and then INSERT records one at a time (by a looping structure) into existing table.

[dskysmine]

Can't do "data_value = data_value + new value" in a query.

Instead of make table, VBA code would have to do the above calc and then INSERT records one at a time (by a looping structure) into existing table.

Yes... the part where I say "but it didn't work" sort of implies that...
My question is how to make into Access VBA something like:

For(i=Count(T_Results);i<0;i--)
{
Define Current Record as T_Results firstrecord
Recordtemp=FindRecord with NodeId = CurrentRecord(T_Results.ParentId)
RecordTemp.Data_Value=RecordTemp.Data_Value+Curren tRecord.Data_Value
}

I know this code doesn't work by the way... it's just an explanation of what I am looking for.
If you could help with this I would appreciate it.

[June7]

One way is to open a recordset of the destination table, add records to the recordset, update will commit the records to table. Review http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Another is to run an SQL INSERT action, showing 3 data types:
CurrentDb.Execute "INSERT INTO tablename(field1, field2, field3) VALUES(" & value1 & ", #" & value2 & "#, '" & value3 & "'"

Loop structure something like
'open recordset of source data for calcs
For i = 1 to rs.RecordCount
'do calc with data from recordset
'code to save record
rs.MoveNext
Next

[dskysmine]

One way is to open a recordset of the destination table, add records to the recordset, update will commit the records to table. Review http://msdn.microsoft.com/en-us/library/bb220954(v=office.12).aspx

Another is to run an SQL INSERT action, showing 3 data types:
CurrentDb.Execute "INSERT INTO tablename(field1, field2, field3) VALUES(" & value1 & ", #" & value2 & "#, '" & value3 & "'"

Loop structure something like
'open recordset of source data for calcs
For i = 1 to rs.RecordCount
'do calc with data from recordset
'code to save record
rs.MoveNext
Next

There's a part missing for the comparison between the T_Results.ParentID to the T_example.ParentID. (the one I put as FIND) Do you know how to put that into VBA?
Thanks!

[June7]

One way is with DLookup.

Value from field of recordset current record could be in the WHERE CONDITION. I am not quite following your code logic, but something like:

DLookup("NodeID", "tablename", "ParentID=" & rs!ParentID)