Cumulative (Running Sum) in a query

Experts, I need my query (in the attached sample db) to return a cumulative amount for each tblDraws_Details1.[Amount] ("Draw Amount" as shown in the pic) however in the pic below, you can see I have a 0 in the first row but what I need to return is 1.2B (the amount of the first Draw Amount) and the next row below it in the same column should be $1,200,000,000 + $12,061,706.00
 = $1,212,061,706.00.  What I am looking for is a running sum for tblDraws_Details1.[Amount] and keeping the same where condition: T.FacilityType=Q_Disbursement.FacilityType

How would an expert do this?  I have taken too much time trying to modify.
thank you

I have attached a database.  
Please open the query:  Q_DisbursementSum

screenshotBalanceEE.accdb
pdvsaProject financeAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
try this:
SELECT Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, Q_Disbursement.FacilityAmount, 
(Select Sum(T.Amount) From Q_Disbursement As T Where T.FacilityType=Q_Disbursement.FacilityType And T.ValueDate <= Q_Disbursement.ValueDate)
AS [Cumulative Drawn],
 Q_Disbursement.Amount, [FacilityAmount]-[Amount] AS Available
FROM Q_Disbursement
GROUP BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate, Q_Disbursement.FacilityAmount, Q_Disbursement.Amount
ORDER BY Q_Disbursement.FacilityType, Q_Disbursement.ValueDate;

Open in new window

0
 
pdvsaProject financeAuthor Commented:
Perfect.  I am going to look that over and see how you did that.  thats amazing.
0
 
pdvsaProject financeAuthor Commented:
I see where the edit is:
Old
And T.ValueDate <= Q_Disbursement.ValueDate)-Sum([Amount]) AS [Cumulative Drawn],
Your solution:
And T.ValueDate <= Q_Disbursement.ValueDate) AS [Cumulative Drawn],

I see my old way subtracted the Sum([Amount]) and that is where I was wrong.  

Unfortunately, I will forget what I learned by tomorrow.   :(

thank you for the help Ryan.
0
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.

All Courses

From novice to tech pro — start learning today.