Complex Access sql problem 1

[borntorun]

Hi,

I have a table with the following columns

area option year cumetotal
aaa 1 98 2
aaa 1 99 4
aaa 1 2000 20
aaa 2 98 3
aaa 2 99 8
aaa 2 2000 12
bbb 1 98 8
bbb 1 99 23
bbb 1 2000 28
bbb 2 98 4
bbb 2 99 9
bbb 2 200 23

So i need to have a column which has the individual total for each area, option, year...... This needs to be calculated from the cume total...

 

[borntorun]

Please note that this can be done in access vba... as well.

 

[PHV]

With your posted input samples, what is the expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bong]

isn't this what a pivot table is for?

_________________
Bob Rashkin

 

[borntorun]

It needs to be in a table so that i can use it in another application that interacts with access through sql, but yes a pivot table would work.
The expected out put is

area option year cumetotal individ Totals
aaa 1 98 2 2
aaa 1 99 4 2
aaa 1 2000 20 16
aaa 2 98 3 3
aaa 2 99 8 5
aaa 2 2000 12 4
bbb 1 98 8 8
bbb 1 99 23 15
bbb 1 2000 28 13
bbb 2 98 4 4
bbb 2 99 9 5
bbb 2 200 23 14

 

[PHV]

Something like this ?
SELECT A.area, A.option, A.year, A.cumetotal, A.cumetotal-Nz(B.cumetotal,0) AS [individ Totals]
FROM yourTable AS A LEFT JOIN yourTable AS B
ON A.area=B.area AND A.option=B.option AND A.year>B.year)
WHERE Nz(B.year,0)=Nz((SELECT Max([year]) FROM yourTable WHERE area=A.area AND option=A.option AND [year]<A.year),0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886