calculate the running sum in a query using DSum

I am trying to calculate the running sum of the number of tasks overdue.

I have a query (qryTasksOverDue) that joins some tables and calculates whether the task is overdue
(isOverDue is a field that equals 1 or 0)

Sample data from this is (filtered where isOverDue is 1)

DateA            isOverDue      
-----                            ---------
9/3/2006                         1      
9/3/2006                         1      
9/10/2006                         1      
10/15/2006         1      
10/15/2006         1      
10/15/2006         1      
10/15/2006         1      

Here is the query I am having problems with

Based on query above (qryTasksOverDue)

StartDate: DateA (Group by)
SumOfisOverDue: isOverDue (sum)
RunningSum : DSum("isOrigOverDue","qryTasksOverDue",[DateA]<=[StartDate]) (expression)

I get:

StartDate      SumOfisOverDue      RunningSum      (SHOULD BE)
---------      --------------      ----------                      -----------
7/2/2006           0            7            (0)
7/9/2006           0            7            (0)            
7/16/2006           0            7            (0)
7/23/2006           0            7            (0)
7/30/2006           0            7            (0)
8/6/2006           0            7            (0)
8/13/2006           0            7            (0)
8/20/2006           0            7            (0)
8/27/2006           0            7            (0)
9/3/2006           2            7            (2)
9/10/2006           1            7            (3)
9/17/2006           0            7            (3)
9/24/2006           0            7            (3)
10/1/2006           0            7            (3)
10/8/2006           0            7            (3)
10/15/2006    4            7            (7)

I have tried many other ways of using DSum but I have not been able to get anything to work. Anyone know how I can get what I want?

Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
Hi tammieR,

Modify qryTasksOverDue to aggregate on the date, and then run a query like this:

SELECT q1.DateA, Sum(q1.isOverDue) AS OverdueOnDate,
    (SELECT Sum(q2.isOverDue) FROM qryTasksOverDue AS q2 WHERE q2.DateA <= q1.DateA) AS RunningTot
FROM qryTasksOverDue AS q1


two things:
1. Are you going to be displaying this data in a report? if so, just use the running sum property in the report instead of doing it in SQL.
2. can you post your SQL instead of the design view of the query
I think that your column RunningSum Should look like this, you might not have copied it right though:

RunningSum : DSum("isOrigOverDue","qryTasksOverDue","[DateA]<=" & [StartDate])
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Patrick MatthewsCommented:
If that does not work, try:

RunningSum : DSum("isOrigOverDue","qryTasksOverDue","[DateA]<=#" & [StartDate] & "#")
good point, I saw the missing quotes, but didn't even think, you have to have the number symbols(#) good pickup matthew
Patrick MatthewsCommented:
It may not be relevant :)
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

  Just a comment; it's been asked where you plan to use this.  If in a form or report, there are better ways then using a sub select or DSum() in the query.  By doing that, you are in affect running a sum query for every record that gets returned.

  For a few records, this won't matter much, but if it's a larger recordset, performance will be poor.

tammieRAuthor Commented:
1. I am planning on using this information in a pivot chart form eventually so I can not just show it in a report. This does work very nicely if I wanted a report.

I also tried putting the quotes on the dsum function like you suggested and it did not give me any results at all. RunningSum was blank.
tammieRAuthor Commented:
I tried this query and it is giving me exactly what I wanted! THANKS!
Patrick MatthewsCommented:
Glad to help :)
In the accepted solution, how do you "Modify qryTasksOverDue to aggregate on the date"? I am trying to adapt this solution to my query, but I can't figure out what matthewspatrick meant.

Thanks for the help.

Best, Matt
All Courses

From novice to tech pro — start learning today.