Running total in a query

[PortyAL]

Hi

Is there any way of showing a running total in a query? e.g.

Month Cost Total to date
April £1000 £1000
May £1500 £2500
June £950 £3450 etc.

Thanks

Alan Cassells

 

[tortoisehalfbreed]

If it's for a report just use the running sum property on the field

 

[PortyAL]

I'd like to use the running total in other calculations

The SQL for the query is as follows:

SELECT [Table A].Month, [Table A].Cost
FROM [Table A];

I want a running total for "Cost".

Thanks

Alan

 

[PHV]

SELECT A.Month, A.Cost, Sum(B.Cost) AS [Total to date]
FROM [Table A] AS A INNER JOIN [Table a] AS B ON A.Month >= B.Month;

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

 

[PortyAL]

Tried that. Got the following error

You tried to execute a query that does not include the specified expression 'Month' as part of an aggregate function.

 

[PHV]

OOps, sorry:
SELECT A.Month, A.Cost, Sum(B.Cost) AS [Total to date]
FROM [Table A] AS A INNER JOIN [Table a] AS B ON A.Month >= B.Month
GROUP BY A.Month, A.Cost;

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