x
Solved

# calculate the running sum in a query using DSum

Posted on 2006-10-24
Medium Priority
2,863 Views
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

StartDate: DateA (Group by)
SumOfisOverDue: isOverDue (sum)

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!
0
Question by:tammieR
• 4
• 3
• 2
• +2

LVL 6

Expert Comment

ID: 17798064
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
0

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 17798124
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
ORDER BY q1.DateA

Regards,

Patrick
0

LVL 6

Expert Comment

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

0

LVL 93

Expert Comment

ID: 17798211
If that does not work, try:

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

LVL 6

Expert Comment

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

LVL 93

Expert Comment

ID: 17798312
It may not be relevant :)
0

LVL 59

Expert Comment

ID: 17798325
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
0

Author Comment

ID: 17798369
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.
0

Author Comment

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

LVL 93

Expert Comment

ID: 17798486
0

Expert Comment

ID: 25203623
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
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.