Running Sum in Query

  • Thread starter Thread starter DRTurner
  • Start date Start date
D

DRTurner

Can anyone help me?

I am creating some reports for the boss and the one I am
working on calls for a chart to be placed at the bottom.
This in itself is quite straightforward, but I am having
trouble with the underlying query.

The query shows the amount spent on spare parts for each
month during 2003. But what the graph needs to show is
the running sum across the whole year (i.e. Jan, Jan+Feb,
Jan+Feb+Mar etc...)

I can do this quite easily in a report, but I am really
strugging to get it done in the graph query.

All suggestions would be most welcome.

Thanks in advance

David
 
Add a calculated field to the query, and use a subquery as its expression:

RunningSumValue: (SELECT Sum(T.Amount) FROM TableName AS T WHERE
T.MonthField <= TableName.MonthField;)

Replace my generic names with your real names (except the T and AS T
parts -- leave those as they are). The above code assumes that you have a
value in the MonthField that would sort such that January is the "minimum"
value, and December is the "maximum" value (such as the MonthValue of a
date).

An alternative way:

RunningSumValue: DSum("Amount", "TableName", "MonthField <= " &
TableName.MonthField")
 
Back
Top