Reset running sum on group level

Hi,

I created a report in MS Access with several group levels.
It reports kilometers by month, by car and by year.
I use the running sum functionality to create cumulatives on detail level.
My problem is that I need these cumulatives by car; I have the choice to
create running sum "Over Group" or "Over All", but neither does the right
thing for me.
If I use "Over Group" it will reset also on MONTH level; if I use "Over All"
it will not reset on CAR level as required.

Is there a solution for this?

sample:
DATE KM's KM;s CUM
CAR #1
02-01-2010 200 200
03-01-2010 100 300
TOTAL MONTH 1 300
05-02-2010 150 450
06-02-2010 200 650
TOTAL MONTH 2 350

TOTAL CAR #1 650 650

CAR #2
02-01-2010 200 200 ----RESET!!!!
03-01-2010 100 300
TOTAL MONTH 1 300
05-02-2010 150 450
06-02-2010 200 650
TOTAL MONTH 2 350

TOTAL CAR #1 650 650

TOTAL YEAR 1300

One method is to do this within the report's record source query with a
subquery. It might look something like this:

SELECT *, (Select SUM(KM) FROM tableA A WHERE A.Car = TableA.Car AND
A.Date=TableA.Date) as CumKMs
FROM TableA;

--
Duane Hookom
Microsoft Access MVP


"Clage" wrote:

Hi,

I created a report in MS Access with several group levels.
It reports kilometers by month, by car and by year.
I use the running sum functionality to create cumulatives on detail level.
My problem is that I need these cumulatives by car; I have the choice to
create running sum "Over Group" or "Over All", but neither does the right
thing for me.
If I use "Over Group" it will reset also on MONTH level; if I use "Over All"
it will not reset on CAR level as required.

Is there a solution for this?

sample:
DATE KM's KM;s CUM
CAR #1
02-01-2010 200 200
03-01-2010 100 300
TOTAL MONTH 1 300
05-02-2010 150 450
06-02-2010 200 650
TOTAL MONTH 2 350

TOTAL CAR #1 650 650

CAR #2
02-01-2010 200 200 ----RESET!!!!
03-01-2010 100 300
TOTAL MONTH 1 300
05-02-2010 150 450
06-02-2010 200 650
TOTAL MONTH 2 350

TOTAL CAR #1 650 650

TOTAL YEAR 1300

Clage,

Another way, which could be significantly faster that the subquery method,
would be:

1. Change the control you are currently using for this to unbound, and get
rid of the running sum

2. In the reports declaration section declare an integer variable:

Private intCarMiles as Integer

3. In the Car group headers format event, set this value to zero:

Private Sub head_Car_Format(Cancel As Integer, FormatCount As Integer)
intCarMiles = 0
End Sub

4. In the detail sections Format event, increment the value of the variable
and store it in the unbound text field:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
intCarMiles = intCarMiles + Me.Mileage
Me.txt_Sum = intCarMiles
End Sub

HTH
Dale

"Clage" wrote in message
...
Hi,

I created a report in MS Access with several group levels.
It reports kilometers by month, by car and by year.
I use the running sum functionality to create cumulatives on detail level.
My problem is that I need these cumulatives by car; I have the choice to
create running sum "Over Group" or "Over All", but neither does the right
thing for me.
If I use "Over Group" it will reset also on MONTH level; if I use "Over
All"
it will not reset on CAR level as required.

Is there a solution for this?

sample:
DATE KM's KM;s CUM
CAR #1
02-01-2010 200 200
03-01-2010 100 300
TOTAL MONTH 1 300
05-02-2010 150 450
06-02-2010 200 650
TOTAL MONTH 2 350

TOTAL CAR #1 650 650

CAR #2
02-01-2010 200 200 ----RESET!!!!
03-01-2010 100 300
TOTAL MONTH 1 300
05-02-2010 150 450
06-02-2010 200 650
TOTAL MONTH 2 350

TOTAL CAR #1 650 650

TOTAL YEAR 1300