Sum totals where null

[bakerboy_1111]

I'm trying to create query based on a tree diagram.

I have a table with the following info: "Upstream_Feed", "Level_No", Tag_Number" and "Power".

Upstream_Feed - ID (Tag_Number) of level connected directly above
Level_Number - current level number (top = 0, next is 1 etc..)
Tag_Number - current level ID
Power - power value

Via this info one can construct the layout of the tree diagram. The purpose is to sum the powers at each level all the way to the top level. I will only enter the power levels manually for the very bottom levels of the tree diagram.

My idea is to create a query that checks to see if the power value is null (which corresponds to all levels except the bottom level). Out of these levels I would like to sum the powers from all the levels where the "Upstream_Feed" equals the current "Tag_Number", and store this value in the "Power" entry.

Does anybody know what code can I use to do this?

Any help is greatly appreciated.

Bakerboy

 

[WayneRyan]

Bakerboy,

Are the depth and width of the tree totally variable? Basically,
can some terminal nodes be at level 5, while others are at
level 7? My queries aren't that strong, but I was thinking
that if the tree was static, then a set of iterative queries
could sum the children grouping by parent. But if it is dynamic,
this may be a VBA exercise.

Sample db?

Wayne

 

[bakerboy_1111]

Thanks for your reply Wayne,

Unfortunately, this is a dynamic tree diagram. (Makes life a bit more complicated!!)

I've attached a sample db if you wanna have a look. I hope it makes sense. Thanks heaps for your help by the way.

Cheers,

Bakerboy

 

[bakerboy_1111]

Of course, actually attaching the file will help!!

 

[WayneRyan]

Baker,

I took the coward's way out and just wrote a quick VBA procedure
to propagate the power up through the tree. I don't think it's
what you really need. But we had to do something while we
wait for some query expertise.

Wayne

 

[bakerboy_1111]

Thanks heaps Wayne,

That appears to be pretty good.

One thing I'm noticing though is that the L1 load coming directly of the main MSB1, is being added twice. I think this is because it is being counted as a terminal node aswell as a load directly off the level. Do you know how to fix this?

Cheers,

Bakerboy

 

[bakerboy_1111]

Sorry, one other thing.

The new PowerSum column is a good idea but I wonder if it's possible to have these totals, aswell as the load totals in the same column.

My idea would be to add to the code, some way of making PowerSum equal to Power for all terminal nodes.

Do you know how to do this?

Thanks again, sorry to be asking so much.

Bakerboy

 

[WayneRyan]

Baker,

I was just playing around with it. The L1 is why I put the
If - End If statement, because it wasn't picking up where it
was a terminal Level 1. I'd have to check it again.

You could get by with just the one-power field, but then
you'd have to recursively sum them up. Not something
that I'd want to do right now.

We did it for another project. Look here under recursion and
see if you want to take it on.

Wayne