A Summing "Heirachy"

[michaelh93]

Hi,

I am working on a database that it used to calculate power consumption. Each item is assigned to a specific substation where it gets it power. So for example if you have 10 1 watt light bulbs attached to the substation, the total power drawn would be 10 watts.

This total power per substation is calculated by a query that sums all of the different values for each item and presents the combined total next to the substation name.

What makes this more difficult is the fact that some substations get their power from other substations, making the total power from the main substation effectively the sum of the components attached to both.

For example: substation A has 10 watts worth of light bulbs and substation B has 15 watts worth of light bulbs. Substation A is "feeding" power to substation B, so the total power drawn from substation A is 25W and the total power drawn from substation B is 15W.

The table that holds the substation data has a field that identifies which substation is gets its power from, assuming it is not independant.

Currently my query can sum all of the components entered under substation A but what I need, but cannot do, is for the total of substation B to be included with the total of substation A. (In the case that A is "feeding" B.)


How can I go about this. It might be simple but I can't think of anything.

Thanks

[Rod]

The feasibility and/or ease of the solution will depend upon your database/table design. Could you post that, preferably as a relationship diagram. In any event the solution will not be trivial.

[michaelh93]

The feasibility and/or ease of the solution will depend upon your database/table design. Could you post that, preferably as a relationship diagram. In any event the solution will not be trivial.

This is my first ever database, and is quite complex (at least to me) so I can assure you the structure is less than ideal. (Probably quite bad, my makeshift solution to this problem is sketchy at best.). It is functional at this point, and if the solution may be difficult it might be in my best intrest to stick with what I have, although when i start testing it may turn out that I need to re-write this.

*Symptomatic of my lack of training in Access is that I haven't used the database wide relationship window :S

[Rod]

OK, good luck.

Let me mention - perhaps for the future - that hierarchies are tricky. Your requirement sounds fairly basic and you can set it up by including a recursive relationship between substations. (Identical situations occur for manager/supervisor/staff and parent/child relationships.) For more formal and/or complex situations there is an ActiveX object called TreeView but the documentation for this is 'patchy.'

Whichever solution you use, trying to sum all nodes in a branch using a single SQL query is a nightmare; I'm not even sure it's possible unless you allow restrictions on the depth of the recursion. Instead you will be involved in what is often called 'chain chasing.' It's not that difficult - somewhat akin to juggling with three balls - but once set up will work for any entry point in the hierarchy and for any depth of hierarchy (levels of recursion).

[michaelh93]

Thank you Rod.

You have been most helpful.

EDIT: Turns out I'll need to implement chain chasing after all, my design criteria has expanded. Wish me luck.

EDIT 2: I can't find any good sources on chain chasing, can you recommend any? Thanks again.