complex access problem 1

[borntorun]

borntorun (MIS) 7 Dec 05 12:42
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......

 

[Golom]

I'm not sure that this is what you're looking for

Select Area, Option, [Year], SUM(CumeTotal) As [Total]

From myTable

Group By Area, Option, [Year]

Unfortunately, for your sample data, that will just give you the same rows with the computed SUM equal to the CumeTotal field because each row has a unique combination of Area, Option and [Year].

Can you provide an example of the output you would expect to see?

 

[borntorun]

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]

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