Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2857
  • Last Modified:

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!
0
tammieR
Asked:
tammieR
  • 4
  • 3
  • 2
  • +2
1 Solution
 
yhwhlivesinmeCommented:
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
 
Patrick MatthewsCommented:
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
ORDER BY q1.DateA

Regards,

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

RunningSum : DSum("isOrigOverDue","qryTasksOverDue","[DateA]<=" & [StartDate])
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Patrick MatthewsCommented:
If that does not work, try:

RunningSum : DSum("isOrigOverDue","qryTasksOverDue","[DateA]<=#" & [StartDate] & "#")
0
 
yhwhlivesinmeCommented:
good point, I saw the missing quotes, but didn't even think, you have to have the number symbols(#) good pickup matthew
0
 
Patrick MatthewsCommented:
It may not be relevant :)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
tammieRAuthor Commented:
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
 
tammieRAuthor Commented:
Matthewspatrick:
I tried this query and it is giving me exactly what I wanted! THANKS!
0
 
Patrick MatthewsCommented:
Glad to help :)
0
 
matthewprestonCommented:
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now