Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Nov 13, 2024
Solved

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...
access
running sum
  • Karl_Donaubauer's avatar
    Karl_Donaubauer
    Nov 14, 2024

    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

Tony2021's avatar
Tony2021
Steel Contributor
Nov 14, 2024

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\#"))

arnel_gp's avatar
arnel_gp
Steel Contributor
Nov 14, 2024

you can't use DSum() on your RunningSum because:

  1. there are ID that have big ID yet earlier date (maybe late entry?)
  2. 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.

  • Tony2021's avatar
    Tony2021
    Steel Contributor
    Nov 14, 2024

    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!  

    • arnel_gp's avatar
      arnel_gp
      Steel Contributor
      Nov 14, 2024

      or a combination of date and ID.

       

      try your suggestion and you will see that the second record has RunningTotal, the same as it's amount (30,700,000.00) when the RunningTotal should be, up to that record, 36,393,629.74.