Running totals in Access queries

[BillShaw]

Using Access 2003 I am trying to construct a query to provide data for a stacked column graph to show cumulative sales figures month on month individually by salesman. Eg. a column will represent a month and will be segmented by salesman. Each subsequent month / bar thereafter will compose of the running total of sales, again segmented by each salesman.

I have a query which has the following fields:

Month
Salesman
SalesVolume$

Therefore at each change in month, I would like a running sum for each individual salesman for the year so far.

Most examples I have seen so far seem to be 1 level more simple than this, eg. based on an example to provide a running total by by salesman and not salesman and month such as article Q233485 at support.microsoft.com

Any solutions will be greatly appreciated!

[puppydogbuddy]

Using Access 2003 I am trying to construct a query to provide data for a stacked column graph to show cumulative sales figures month on month individually by salesman. Eg. a column will represent a month and will be segmented by salesman. Each subsequent month / bar thereafter will compose of the running total of sales, again segmented by each salesman.

I have a query which has the following fields:

Month
Salesman
SalesVolume$

Therefore at each change in month, I would like a running sum for each individual salesman for the year so far.

Most examples I have seen so far seem to be 1 level more simple than this, eg. based on an example to provide a running total by by salesman and not salesman and month such as article Q233485 at support.microsoft.com

Any solutions will be greatly appreciated!

Try using the crosstab query wizard, responding to the prompts as follows:
Data source: Your existing query
column header: salesman
value column: SalesVolume$
RowHeader: Month

[BillShaw]

Try using the crosstab query wizard, responding to the prompts as follows:
Data source: Your existing query
column header: salesman
value column: SalesVolume$
RowHeader: Month

Thanks for the suggestion, puppydogbuddy. Unfortunately this generates a simple total for each month, broken down for each salesman but not running / cumulative which was the objective.

[hrford]

If this is in a report you can do a =Sum([Jan]+[feb]+...) which should work....

[puppydogbuddy]

Thanks for the suggestion, puppydogbuddy. Unfortunately this generates a simple total for each month, broken down for each salesman but not running / cumulative which was the objective.

try adding alias column using DSum("[SalesVolume$]", "yourSourceQuery", "[Salesman] <= '" & [Salesman] & "'")

you may have to change Month to ColumnHeader and make Salesman the rowHeader in order for the DSum to work as intended.
........or

use crosstab as record source for a report and group by salesman; set running sum property of control used for running sum to yes,

[missinglinq]

hrford tried to post this but hit "Report" rether than Post Reply:

I'm a relative newbie but can't you write an expr in you report like in
the report footer that reads

=Sum([Jan]+[Feb]+...)