Cumulative sum in query

  • Thread starter Thread starter Jean-Marie
  • Start date Start date
J

Jean-Marie

I am building a database to manage a fleet of transport
vehicles. My system is Access 2000/Win XP Pro.
I have among other tables
- tblVehicles (LicensePlate (PK), ChassisNo, AcqDate,
HorsePower, MotorType),
- tblRevenue (IDRevenue, DateRev, AmountRev, VehicleRev,
DriverRev, CommentRev).
I have built a query that displays the summary revenue
per week for a given vehicle, something looking like this.
----------------------------------
Week # Revenue
Week1 Rev1
Week2 Rev2
..... ....
Weekn Revn
-----------------------------------

Now I need to build another one that gives the cumulative
revenue per week, something like the following
--------------------------------------------
Week # Revenue
Week1 Rev1
Week2 Rev1+Rev2
..... ....
Weekn Rev1+Rev2+ . +Revn
-------------------------------------------------
I don't know how to get there. Can someone help me,
please?

Tanks in advance and Merry Christmas

Jean-Marie.
 
Jean-Marie said:
I am building a database to manage a fleet of transport
vehicles. My system is Access 2000/Win XP Pro.
I have among other tables
- tblVehicles (LicensePlate (PK), ChassisNo, AcqDate,
HorsePower, MotorType),
- tblRevenue (IDRevenue, DateRev, AmountRev, VehicleRev,
DriverRev, CommentRev).
I have built a query that displays the summary revenue
per week for a given vehicle, something looking like this.
----------------------------------
Week # Revenue
Week1 Rev1
Week2 Rev2
.... ....
Weekn Revn
-----------------------------------

Now I need to build another one that gives the cumulative
revenue per week, something like the following
--------------------------------------------
Week # Revenue
Week1 Rev1
Week2 Rev1+Rev2
.... ....
Weekn Rev1+Rev2+ . +Revn
-------------------------------------------------
Click to expand...


I think this the kind of query you're looking for:

SELECT [Week#],
(SELECT Sum(Q.[Revenue])
FROM query1 As Q
WHERE Q.[Week#] <= query1.[Week#]
) As CumRevenue
FROM query1

But, this is even easier to do in a report base on the first
query and settin the Revenue text box's RunningSum property
to Over All.
 
Thanks Marshal.

I will test your advice and let you know how it went.
I need the query because I will then base on it a chart
representing the cumulative revenue as against the week
numbers.
Thanks again.

-----Original Message-----
Jean-Marie said:
I am building a database to manage a fleet of transport
vehicles. My system is Access 2000/Win XP Pro.
I have among other tables
- tblVehicles (LicensePlate (PK), ChassisNo, AcqDate,
HorsePower, MotorType),
- tblRevenue (IDRevenue, DateRev, AmountRev, VehicleRev,
DriverRev, CommentRev).
I have built a query that displays the summary revenue
per week for a given vehicle, something looking like this.
----------------------------------
Week # Revenue
Week1 Rev1
Week2 Rev2
.... ....
Weekn Revn
-----------------------------------

Now I need to build another one that gives the cumulative
revenue per week, something like the following
--------------------------------------------
Week # Revenue
Week1 Rev1
Week2 Rev1+Rev2
.... ....
Weekn Rev1+Rev2+ . +Revn
-------------------------------------------------
Click to expand...


I think this the kind of query you're looking for:

SELECT [Week#],
(SELECT Sum(Q.[Revenue])
FROM query1 As Q
WHERE Q.[Week#] <= query1.[Week#]
) As CumRevenue
FROM query1

But, this is even easier to do in a report base on the first
query and settin the Revenue text box's RunningSum property
to Over All.
Click to expand...
 
I tested it and worked perfect.

Many thanks again.

Jean-Msarie
-----Original Message-----
Thanks Marshal.

I will test your advice and let you know how it went.
I need the query because I will then base on it a chart
representing the cumulative revenue as against the week
numbers.
Thanks again.

-----Original Message-----
Jean-Marie said:
I am building a database to manage a fleet of transport
vehicles. My system is Access 2000/Win XP Pro.
I have among other tables
- tblVehicles (LicensePlate (PK), ChassisNo, AcqDate,
HorsePower, MotorType),
- tblRevenue (IDRevenue, DateRev, AmountRev, VehicleRev,
DriverRev, CommentRev).
I have built a query that displays the summary revenue
per week for a given vehicle, something looking like this.
----------------------------------
Week # Revenue
Week1 Rev1
Week2 Rev2
.... ....
Weekn Revn
-----------------------------------

Now I need to build another one that gives the cumulative
revenue per week, something like the following
--------------------------------------------
Week # Revenue
Week1 Rev1
Week2 Rev1+Rev2
.... ....
Weekn Rev1+Rev2+ . +Revn
-------------------------------------------------
Click to expand...


I think this the kind of query you're looking for:

SELECT [Week#],
(SELECT Sum(Q.[Revenue])
FROM query1 As Q
WHERE Q.[Week#] <= query1.[Week#]
) As CumRevenue
FROM query1

But, this is even easier to do in a report base on the first
query and settin the Revenue text box's RunningSum property
to Over All.
Click to expand...
.
Click to expand...
 
Back
Top