asked on

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?

Thanks!

yhwhlivesinme

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

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yhwhlivesinme

I think that your column RunningSum Should look like this, you might not have copied it right though:

RunningSum : DSum("isOrigOverDue","qryTasksOverDue","[DateA]<=" & [StartDate])

Patrick Matthews

If that does not work, try:

RunningSum : DSum("isOrigOverDue","qryTasksOverDue","[DateA]<=#" & [StartDate] & "#")

yhwhlivesinme

good point, I saw the missing quotes, but didn't even think, you have to have the number symbols(#) good pickup matthew

Patrick Matthews

It may not be relevant :)

Jim Dettman (EE MVE)

Tammie,

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.

JimD

tammieR

ASKER

yhwhlivesinme:
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.

tammieR

ASKER

Matthewspatrick:
I tried this query and it is giving me exactly what I wanted! THANKS!

Patrick Matthews

Glad to help :)

matthewpreston

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