If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Help: Sum values up a tree

I have a small MS Access DB that is in essence an accounting journal. But each record is flat except for links to look up tables. I want to be able to arrange the records in a tree and then sum the values from individual records to a top node.

I don't want each record to identify its parent in the tree, because I would like to add nodes and move records from one node to another.

I am very poor at scripts and VB for Access. It would be nice to somehow do this via SQL.

I might utilize the dlookup command against a query (if not huge recordset) putting this function or dlookup command in the field's source and put the summing/criteria in the query itself (ie. basing it off of the same node's listing or the records you want to sum) (or write a function to open the recordset and retrieve the sum using whatever criteria.)

I haven't worked with nodes but this is often how I approach multiple listboxes or summing a specific set of records to return the value to a field on any form. A function is ideal since it will be the fastest versus a dlookup.

example of a function returning a value (you would then put =retSumOfXX() in the field's source).

I might utilize the dlookup command against a query (if not huge recordset) putting this function or dlookup command in the field's source and put the summing/criteria in the query itself (ie. basing it off of the same node's listing or the records you want to sum) (or write a function to open the recordset and retrieve the sum using whatever criteria.)

I haven't worked with nodes but this is often how I approach multiple listboxes or summing a specific set of records to return the value to a field on any form. A function is ideal since it will be the fastest versus a dlookup.

example of a function returning a value (you would then put =retSumOfXX() in the field's source).

Trees are defined by a lineage of parent/child relationships. You "move" nodes by changing the identified parent.

What are you driving at here?

You are not as lost as you think.
In trying to explain myself, I found the following concepts:
1) There is only one table. It's records belong only to leaf nodes.
2) Each record identifies the leaf node it is associated with.
3) Higher nodes have no records, but only values that are the sums of subnodes.
4) Moving a record from one node to another, simply requires changing the parent node identifier.

I know how to get a total of the values of the leaf node records with a simple SQL command. But how do I then move up the tree summing the values as I go?

That's painful to do in Access since it has no concept of hierarchal data types.

I haven't tried to do this on my own, but I think the answer would be you have to do it by hand. That is to say walk the tree programmatically and either store the id's that you care about in some kind of temporary storage container so you can sum them with one query later, or keep track of a running sum while you go.

What are you doing as far as generating and displaying the tree? Maybe there's another option to throw in some hooks earlier in the process.

That's painful to do in Access since it has no concept of hierarchal data types.

I haven't tried to do this on my own, but I think the answer would be you have to do it by hand. That is to say walk the tree programmatically and either store the id's that you care about in some kind of temporary storage container so you can sum them with one query later, or keep track of a running sum while you go.

What are you doing as far as generating and displaying the tree? Maybe there's another option to throw in some hooks earlier in the process.

well, to further this design, I thought of a 2nd table with the following fields:
- A node number (identifying this node)
- A parent node number (there may be many subnodes to a parent, but each record is linked to only one parent)
- A subtotal of all subnodes to the current record.

You are correct though; one would have to procedurally iterate summing up the tree. I can't think of any SQL statements that would do that without being in a procedure.

And MS Access is not necessary. One could simply write this application in Java, lets say.

But I can't help but think that there is a very obvious, simple way that just has not yet hit me.