A running sum and filters

[Marin]

Hello,

I need a little help with an outcome of a running sum (cumulative total) after a filter has been applied.



Above is a simplified table on my report.
After applied the date filter, lets say: Date = "Mar" I have got


But I need:


Any Ideas how to get an outcome from the last table?

Thy

[orange]

Tried google to get a MsAccess running total in a report

I found this http://office.microsoft.com/en-us/ac...005187388.aspx

Good luck

[burrina]

Here is an example of a running sum for a field. =RunSum([Form],"RegID",[RegID],"Amount")
I use a module for this as well. Here is the code for the module if your interested. The mod is named ModRunSum

Option Compare Database

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

[chinaguy]

Hi Marin,

I am looking for the same thing. Did you veer find a solution?

Hope you did!

Thanks.

[June7]

Did you try burrina's suggested code? Possible alternatives:

A textbox in report header that sums all the records prior to Mar excluded by the filtered query. This can be in a subreport or DSum() function in textbox on main report. Then include that calculated value in the running sum textbox expression by reference to the textbox.

Or try a nested subquery to get the aggregate calc. http://allenbrowne.com/subquery-01.html#YTD