Summing or grouping 1

[MicroZealous]

I have a simple Budget database, with expense line items that relate to Organization Units. The Organization Units are in a tree structure, with each Unit relating to one Unit that is higher up the tree. Currently, there are three levels, but I need to provide for four or five in the future. I can sum up the amounts for each Unit easily enough, but I need to also include line items from all related lower levels, if any. I have an Organization table with a row for each Unit that includes the ID of the Unit it relates to in the next level up. This seems like a common application, but I’ve had the flu for the last week and can’t seem to focus enough to design the query. Thanks in advance.

 

[vbahelp07]

can you show some sample data?

hope you're feeling better.

 

[MicroZealous]

See if this makes sense:
Here’s a sample:

UNIT table
ID NextLevelUp
A
B A
C B
D B

BUDGET table
UNIT AMOUNT
C 2
C 3
B 1
B 4
A 6
A 5
D 9

Sum AMOUNT for each level, and levels below. Results should be:

D 9
C 5 (2+3)
B 19 (1+4) + C level + D level
A 30 (6+5) + B level

 

[MicroZealous]

I AM feeling better, thanks. I'll be even better once this puzzle gets solved. I can't imagine that it's very difficult, but I've looked at it so long I can see it anymore, ya know?

 

[PHV]

Create a query named, say, qryUnits:

SELECT U0.ID AS ID0, U1.ID AS ID1, U2.ID AS ID2, U3.ID AS ID3
FROM ((UNIT AS U0
LEFT JOIN UNIT AS U1 ON U1.NextLevelUp = U0.ID)
LEFT JOIN UNIT AS U2 ON U2.NextLevelUp = U1.ID)
LEFT JOIN UNIT AS U3 ON U3.NextLevelUp = U2.ID;

And now to get your result:

SELECT ID0 AS ID, Sum(AMOUNT) AS Total
FROM (SELECT ID0, ID0 AS ID1 FROM qryUnits
UNION SELECT ID0, ID1 FROM qryUnits WHERE ID1 Is Not Null
UNION SELECT ID0, ID2 FROM qryUnits WHERE ID2 Is Not Null
UNION SELECT ID0, ID3 FROM qryUnits WHERE ID3 Is Not Null
) AS A INNER JOIN BUDGET AS B ON A.ID1=B.UNIT
GROUP BY ID0;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

MicroZealous, did you try my suggestion ?

 

[MicroZealous]

PHV: Thanks so much for your reply - I appreciate the help a lot. I've been offline recovering from the URI that's going around. Yes, I did try your solution, and it works fine. Doing some more research on this problem, I discovered that this is a common and complex problem. See

http://www.tdan.com/view-special-features/5400

for an in-depth discussion of various approaches. The solution I implemented is a 'brute force' method that uses a unique summing query for each level. It's not the elegant approach that I hoped existed, but it works in my context. Again, thanks for your help.