Help With Running Total Query

**Njliven** · 07-30-2014, 07:20 AM

I need a query to do a running total.
My table has two values - Actual Charges and Project Charges for each month. I need the query to do a running total on each of these fields. So it looks like this

Month-Year	Projected Charges	Actual Charges	Projected (Accumulative)	Actual (Accumulative)
Mar-14	$ -	$ 5,000.00	$ -	$ 5,000.00
Apr-14	$ 500.00	$ 40,000.00	$ 500.00	$ 45,000.00
May-14	$ 500.00	$ 20,000.00	$ 1,000.00	$ 65,000.00
Jun-14	$ 1,000.00	$ 60,000.00	$ 2,000.00	$ 125,000.00

Thanks for any help I can get.

**lfpm062010** · 07-30-2014, 10:18 AM

Sort by Month-year then use "DSUM" to sum up to the "Month-Year".

**Njliven** · 07-30-2014, 01:09 PM

Originally Posted by lfpm062010

Sort by Month-year then use "DSUM" to sum up to the "Month-Year".

I am not familiar with the DSUM function. So it is not making any sense why I would sum the month-Year, when I want to show a running total of the actual charges and the projected charges. Can you maybe provide more explanation.
Do I need SQL or can I use the build query function and bring in all the field names and make the running total a calculated field?

I was able to use the following SQL and get it run, but it did not give the results I wanted. So it should sum amount for jan-feb, then the next line should be a sum for jan-feb-march etc.

SELECT RecordID, Month_Year, Projected_Chrgs,

(SELECT Sum(tbl_PROJECTED_ACTUAL.Projected_Chrgs) AS RunningSum

FROM tbl_Projected_Actual

WHERE tbl_Projected_Actual.ProjectID <= T1.ProjectID) AS RunningSum

FROM tbl_Projected_Actual AS T1

**lfpm062010** · 07-30-2014, 01:51 PM

Code is not tested. More on DSUM http://www.techonthenet.com/access/f...omain/dsum.php

SELECT Month_Year, Projected_Chrgs, DSum("Actual_Chrgs", "tbl_Projected_Actual", "Month_Year <= '" & tbl_Projected_Actual.Month_Year & "'") AS RUNNING_ACTUAL, DSum("Projected_Chrgs", "tbl_Projected_Actual", "Month_Year <= '" & tbl_Projected_Actual.Month_Year & "'") AS RUNNING_PROJECTED
FROM tbl_Projected_Actual
ORDER BY Month_Year;

**June7** · 07-30-2014, 03:11 PM

Or build report and do calc on report. Textbox on report has a RunningSum property.

**Njliven** · 07-30-2014, 03:18 PM

Here is the results I get back. I added to bring back the Actual_Chrgs field. I double checked all the spelling of the table and field names, but it gives the error in the calculated field. Any suggestions? I will also be filtering this to query as a subform and connecting the value of a ProjectID. Should the query also group by ProjectID or will it worked filtered in the form? I tried using the ProjectID instead of Month_Year and it did bring back a number, but it brought back the same number for every record of 945,000????

Month-Year	Projected Charges	Actual Charges	RUNNING_ACTUAL	RUNNING_PROJECTED
Mar-14		$5,000.00	#Error	#Error
Apr-14	$500.00	$45,000.00	#Error	#Error
May-14	$1,000.00	$65,000.00	#Error	#Error
Jun-14	$2,000.00	$125,000.00	#Error	#Error
Jul-14	$8,000.00	$135,000.00	#Error	#Error
Aug-14	$16,000.00	$135,000.00	#Error	#Error
Sep-14	$32,000.00	$195,000.00	#Error	#Error
Oct-14	$64,000.00	$240,000.00	#Error	#Error
Nov-14	$128,000.00		#Error	#Error
Dec-14	$225,000.00		#Error	#Error
Jan-15	$260,000.00		#Error	#Error
Feb-15	$285,000.00		#Error	#Error
Mar-15	$295,000.00		#Error	#Error
Apr-15	$298,000.00		#Error	#Error
May-15	$300,000.00		#Error	#Error

**June7** · 07-30-2014, 04:24 PM

Did you consider the report option?

**Njliven** · 07-30-2014, 06:32 PM

Originally Posted by June7

Did you consider the report option?

I am going to need a report with this data also. I also need to have it show up on a form, so when the figures are entered it will display the running totals along with charts that will also update. Then I will have to have an option to print the report with the graphs as well. Right now I am just trying to get the running total to work, so I can move forward with the rest of it. I will need the query to create the graphs as well.

**June7** · 07-30-2014, 06:36 PM

Report allows display of detail records as well as summary calculations.

Running total on form or in query not simple. ifpm has offered method that should work. Review http://support.microsoft.com/kb/290136

However, domain aggregate functions can really slow down queries and forms.

What do you want to graph?

**Njliven** · 07-30-2014, 07:09 PM

Originally Posted by June7

Report allows display of detail records as well as summary calculations.

Running total on form or in query not simple. ifpm has offered method that should work. Review http://support.microsoft.com/kb/290136

However, domain aggregate functions can really slow down queries and forms.

What do you want to graph?

I am going to need one graph to show the actual and projected amounts, this one will be easy.
Then I will need one that show the running total projected and the running totals actual.

**June7** · 07-30-2014, 10:24 PM

You want to graph each YrMo? Never tried to graph a running total.

Did you get the query working?

**burrina** · 07-30-2014, 11:49 PM

To get a running sum in a query, try this. First copy code below and add to a new module and name ModRunSum

Function RunSum(F As Form, KeyName As String, KeyValue, _
FieldToSum As String)
'************************************************* **********
' FUNCTION: RunSum()
' PURPOSE: Compute a running sum on a form.
' PARAMETERS:
' F - The form containing the previous value to
' retrieve.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldToSum - The name of the field in the previous
' record containing the value to retrieve.
' RETURNS: A running sum of the field FieldToSum.
' EXAMPLE: =RunSum(Form,"ID",[ID],"Amount")
'************************************************* **********
Dim rs As DAO.Recordset
Dim result

On Error GoTo Err_RunSum

' Get the form Recordset.
Set rs = F.RecordsetClone

' Find the current record.
Select Case rs.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_Long, DB_CURRENCY, _
DB_SINGLE, DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
GoTo Bye_RunSum
End Select

' Compute the running sum.
Do Until rs.BOF
result = result + rs(FieldToSum)

' Move to the previous record.
rs.MovePrevious
Loop

Bye_RunSum:
RunSum = result
Exit Function

Err_RunSum:
Resume Bye_RunSum

End Function

HTH

**lfpm062010** · 07-31-2014, 06:58 AM

Originally Posted by Njliven

Here is the results I get back. I added to bring back the Actual_Chrgs field. I double checked all the spelling of the table and field names, but it gives the error in the calculated field. Any suggestions? I will also be filtering this to query as a subform and connecting the value of a ProjectID. Should the query also group by ProjectID or will it worked filtered in the form? I tried using the ProjectID instead of Month_Year and it did bring back a number, but it brought back the same number for every record of 945,000????

Month-Year	Projected Charges	Actual Charges	RUNNING_ACTUAL	RUNNING_PROJECTED
Mar-14		$5,000.00	#Error	#Error
Apr-14	$500.00	$45,000.00	#Error	#Error
May-14	$1,000.00	$65,000.00	#Error	#Error
Jun-14	$2,000.00	$125,000.00	#Error	#Error
Jul-14	$8,000.00	$135,000.00	#Error	#Error
Aug-14	$16,000.00	$135,000.00	#Error	#Error
Sep-14	$32,000.00	$195,000.00	#Error	#Error
Oct-14	$64,000.00	$240,000.00	#Error	#Error
Nov-14	$128,000.00		#Error	#Error
Dec-14	$225,000.00		#Error	#Error
Jan-15	$260,000.00		#Error	#Error
Feb-15	$285,000.00		#Error	#Error
Mar-15	$295,000.00		#Error	#Error
Apr-15	$298,000.00		#Error	#Error
May-15	$300,000.00		#Error	#Error

Try this.

SELECT tbl_Projected_Actual.[Month-Year], tbl_Projected_Actual.Projected_Chrgs, tbl_Projected_Actual.Actual_Chrgs, CDbl(DSum("Projected_Chrgs","tbl_Projected_Actual" ,"CDate([Month-Year]) <= #" & CDate([tbl_Projected_Actual].[Month-Year]) & "#")) AS RUNNING_PROJECTED, CDbl(DSum("Actual_Chrgs","tbl_Projected_Actual","C Date([Month-Year]) <= #" & CDate([tbl_Projected_Actual].[Month-Year]) & "#")) AS RUNNING_ACTUAL
FROM tbl_Projected_Actual
ORDER BY CDate(tbl_Projected_Actual.[Month-Year]);

As June has mentioned, with large amount of data the DSum will run slower.

**Njliven** · 07-31-2014, 09:14 AM

I am not familiar with Modules. I copied and pasted the code you provided into a module. It is called Module1 and would not let me change the name.
Where you say NEXT: =RunSum.......
I am a little confused as to where this code would be placed. If this is a query based on the table where my values are, can it be attached to an unbound text field to show the running total? I was also confused about the [SomeField]. Should this be a field in the table, then show that field in the form to show the running total?
I have been playing around with the query, but have not come up with anything that is workable yet. Thanks!

**lfpm062010** · 08-02-2014, 09:01 AM

Did you try the SQL query on Post #13?

Help With Running Total Query

Thread Tools

Help With Running Total Query

Similar Threads

Running Total Query??

running or sub total using sql query in access

Using running total in query to create graphs

total records after running query and problem with macro error 2950

Creating a Daily Running Total Query

Posting Permissions