Summing and updating table

Hello all,

I have a table at the moment that has the following columns:

Parent_Tag, Child_Tag, Value and Level.

This table represents a tree diagram. The Parent_Tag is the name of the level connected directly above the Child_Tag.

The Child_Tag has an associated Value and Level. The Value is just a number that will be summed, and the Level represents how far down the tree diagram the current Child_Tag is. That is, 0 for top level, 1 for next level etc.

This table has been generated using an append query. Basically, a Child_Tag will have a value only if it is not a Parent_Tag for any other Child_Tag (i.e. if it is at the end of a tree branch). All other Value entries will be Null.

For all these Null values, I would like to add up the Values connected below.

My idea is that I should be able to make an update query that does the following:

- checks to see if a Child_Tag has a Null Value
- Sum the Values for the cases where these Child_Tags are Parent_Tags
- store the summed Value back in the table to replace the Null

As far as I can tell, this will have to be a loop, because the lowest levels will have to be summed before the top ones.

Does anyone know how to design this? Any help will be greatly appreciated.

Cheers,

Bakerboy

PS: I've attached a sample database table to help explain.

Look at this DB

Run the following queries in this order.

SumValuesWhereTheseChildsAreParents
StoreSumValuesBackToTableToReplaceNulls

If I understood you request, this should be what you want

S-

Thankyou so much for your reply, I've been working on this for weeks!!

What you've done is almost what I require, but the only problem is that the results aren't exactly correct. But I really appreciate the help nonetheless!!! :-)

For example, SB3 has L9 connected to it, and L9's value is 4. The sum for SB3 is correct at 4. But SB3 connects to SB2. SB2 also has loads L7 and L8 connected to it whose values are 3 and 5 respectively. The total sum for SB2 comes out at 8, when it should be 12 (total should be L7+L8+SB3). I think this is because it's doing the SB2 sum at the same time as the SB3 sum, and is therefore not including the SB3 total.

This was the reason why I thought this may have to be done with a loop of some kind, maybe summing the totals of the highest level first, then progressively down until it reaches level 0 (top level). The only problem is I have no idea how to do this. Do you know how to modify this so that it will include all totals?

Thanks again for your response,

Bakerboy