Reset running sum on group level

  • Thread starter Thread starter Clage
  • Start date Start date
C

Clage

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;
 
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
 
Back
Top