Running Total in a query

acura123

Board Regular
Joined
Jul 13, 2003
Messages
77
I am trying to create a running total query using dsum without much luck.

The fields are simple:

Date Employee Sales

Is there a simple way of doing this in a query?


Thanks!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
It is very difficult to help when we can't see what you have done so far. Could you post the SQL for this query you have tried so far?
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi acura,

This is very easy to do in a report, not so easy to do in a query.
Consider creating a report based on the query, and building a running sum in the report. The Help files will point you in the right direction.

Denis
 

acura123

Board Regular
Joined
Jul 13, 2003
Messages
77
The Proplem is that I want to create a line chart report based on a running total query.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

There is a way to do it via code -- see this link -- but it can be a bit buggy.

An alternative might be to write out the data to a dummy table, and base the chart on that (this would involve code to create the records, possibly like this) : or to pull the data into Excel, create the running sum there, and chart it using Excel's charting tools which are much easier to use.

Stop press -- just found this example from here.
The running sum query is a simple variant on an auto number query where the query or recordset includes a sub-query as a field to calculate the running sum. As with an auto number query, to create a running sum requires that your query have at least one field which has a unique value for each row.

The running sum simply uses the sub-query to sum all the values in rows where the unique field value is are less than or equal to the value of the current field. Here's an example:

RunningSum: (Select Sum (OrderTotal) FROM [Orders] as Temp
WHERE [Temp].[OrderID] <= [Orders].[OrderID])
Denis
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
acura123,
If you are getting a syntax error, and we have no idea what your code looks like, it is extreamly difficult to help without seeing the SQL for the query you are running. If you post the SQL along with "I'm getting a syntax error", we would have something to go on to help.
 

Forum statistics

Threads
1,141,929
Messages
5,709,398
Members
421,634
Latest member
Spicoli21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top