Forum Discussion
Dsum
Hello experts,
I need to make a running sum total on tblDrawsDetails.Amount but noting the where clause.
Please see attached accdb and xls file.
I have the following but it just returns the grand total of Amount but I need it to sum where the ID< ID
RunningSum: (DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] And [ID]<=[ID]))
SELECT tblDrawsDetails.ID, tblDrawsDetails.FacIDfk, tblDrawsDetails.Amount, tblDrawsDetails.DateRecd, (DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] And [ID]<=[ID])) AS RunningSum, Year([DateRecd])*12+DatePart('m',[DateRecd])-1 AS MonthYrSort, First(Format([Daterecd],"yyyy"", ""mmmm")) AS FundingDateGrp
FROM tblDrawsDetails
GROUP BY tblDrawsDetails.ID, tblDrawsDetails.FacIDfk, tblDrawsDetails.Amount, tblDrawsDetails.DateRecd, Year([DateRecd])*12+DatePart('m',[DateRecd])-1
ORDER BY Year([DateRecd])*12+DatePart('m',[DateRecd])-1;
Hi,
The criteria concatenation in
DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] And [ID]<=[ID])) AS RunningSum
should be
DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] & " And ID<=" & [ID])) AS RunningSum
Servus
Karl
****************
Access Forever News DevCon
Access-Entwickler-Konferenz AEK
8 Replies
ahh I had the quotes messed up. I didnt notice that.
It worked perfectly.
I did end up basing it on a date instead of an ID:
RunningSumDate: DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] & " And nz([DateRecd],0) <=" & Format$(Nz([DateRecd],0),"\#mm\/dd\/yyyy\#"))
you can't use DSum() on your RunningSum because:
- there are ID that have big ID yet earlier date (maybe late entry?)
- there are dates that fall on the same date (facIDfk = 3 , sep-26-2023, 3 records).
so your last hope is the function i provided.
Hi Arnel,
Yes, you are correct with your points above.
I dont think #2 is as concerning though since I think <= on [DateRecd] would capture those records where they are on the same dates.
Could the function be revised to base it on the [DateRecd] instead of the [ID]? If too time consuming I can use the Dsum since it does work.
RunningSum: DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] & " And nz([DateRecd],0) <=" & Format$(Nz([DateRecd],0),"\#mm\/dd\/yyyy\#"))
thank you sir!
you again create a custom function and call it from your query.
see query1 and the function in module1. also open form1 and on design view
see the code on the Load event of the subform where it resets first the recordset.
unable to add attachment again, so here is the link:
Hi Arnel, I didnt see your response. This new website is taking some time getting use to.
The function is amazing approach. Wow.
I think I need it based on [DateRecd] since there could be a case where date is better than ID. I do have cases like this. Could you kindly modify the function to make it based on [DateRecd]? thank you very much!
here is a paste of the module:
Public Function fnRunningSum(ByVal ID As Long, ByVal facID As Long) As Double
' arnelgp
'
' note on ID
'
' if you are using this function in a query and using form
' you can pass -1 to id to close the recordset
' or 0 to requery it
' you other long integer to process the running sum.
'
Static rs As DAO.Recordset
Dim running_total As Double
If ID < 0 Then
If Not (rs Is Nothing) Then
rs.Close
End If
Set rs = Nothing
Exit Function
End If
If rs Is Nothing Then
Set rs = CurrentDb.OpenRecordset("select ID, facIDfk, Amount from tblDrawsDetails Order by facIDfk, dateRecd, ID;", dbOpenSnapshot, dbReadOnly)
End If
If ID = 0 Then
rs.Requery
Exit Function
End If
With rs
.FindFirst "ID = " & ID
Do Until .BOF
If !FacIDfk <> facID Then
Exit Do
End If
running_total = running_total + Nz(!Amount, 0)
.MovePrevious
Loop
End With
fnRunningSum = running_total
End Function
Hi,
The criteria concatenation in
DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] And [ID]<=[ID])) AS RunningSum
should be
DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] & " And ID<=" & [ID])) AS RunningSum
Servus
Karl
****************
Access Forever News DevCon
Access-Entwickler-Konferenz AEK
