How Do I Create A Query That Will Give Me A Cumulative Total?

I have a table with the following columns: reportDate DATETIME and losses CURRENCY and of course the ID column. How do I write a query that will return a table with a running total

Solution 1:

I found table and field names in the edit history of your question, so used those names in this answer. You didn't provide record_matYields sample data, so I created my own and hope it is suitable:

id reportDate gainOrLoss
 1 12/28/2011  $1,500.00
 2 12/29/2011    $500.00
 3 12/30/2011  $1,000.00
 4   1/2/2012     $10.00
 5   1/3/2012  $4,500.00
 6   1/4/2012    $900.00

First I created qryMonthlyLosses. Here is the SQL and the output:

SELECTYear(reportDate) AS reportYear,
    Month(reportDate) AS reportMonth,
    Min(y.reportDate) AS MinOfreportDate,
    Sum(y.gainOrLoss) AS SumOfgainOrLoss
FROM record_matYields AS y
GROUPBYYear(reportDate),
    Month(reportDate);

reportYear reportMonth MinOfreportDate SumOfgainOrLoss
      20111212/28/2011       $3,000.00201211/2/2012       $5,410.00

I used that first query to create another, qryCumulativeLossesByMonth:

SELECT
    q.reportYear,
    q.reportMonth,
    q.MinOfreportDate,
    q.SumOfgainOrLoss,
    (
        SELECT
        Sum(z.gainOrLoss)
        FROM record_matYields AS z
        WHERE z.reportDate < q.MinOfreportDate
    ) AS PreviousGainOrLoss
FROM qryMonthlyLosses AS q;

reportYear reportMonth MinOfreportDate SumOfgainOrLoss PreviousGainOrLoss
      20111212/28/2011       $3,000.00201211/2/2012       $5,410.00          $3,000.00

Finally I used qryCumulativeLossesByMonth as the data source in a query which transforms the output to match your requested format.

SELECT
    q.reportYear,
    MonthName(q.reportMonth) AS [Month],
    q.SumOfgainOrLoss AS Losses,
    q.SumOfgainOrLoss +
        IIf(q.PreviousGainOrLoss Is Null,0,q.PreviousGainOrLoss)
        AS Cum
FROM qryCumulativeLossesByMonth AS q;

reportYear Month    Losses    Cum
      2011 December $3,000.00 $3,000.002012 January  $5,410.00 $8,410.00

You could probably revise this into a single query using subqueries instead of the separate named queries. I used this step-wise approach because I hoped it would be easier to understand.

Edit: I returned the full name with the MonthName() function. If you want the abbreviated month name, pass True as a second parameter to that function. Either of these should work:

MonthName(q.reportMonth, True) AS [Month]MonthName(q.reportMonth, -1) AS [Month]

Solution 2:

This page looks good for you:

http://support.microsoft.com/kb/290136

FYI, I wrote the following T-SQL against SQL Server before:

createtable #a (key_col int, val int)

insertinto #a values (1, 10)
insertinto #a values (2, 10)
insertinto #a values (3, 30)
insertinto #a values (4, 10)

select x.key_col,x.val,sum(y.val) as cumulated
from #a x 
innerjoin #a y on
x.key_col >= y.key_col
groupby x.key_col,x.val
orderby x.key_col,x.val

droptable #a

The result:

 key_col     val         cumulated   
 ----------- ----------- ----------- 
           11010210203305041060

Solution 3:

This will do it for you in one SQL without using temporary tables

SELECT Format$([TranDate],"yyyy mm") AS mthYear, First(DSum("[GainOrLoss]","[Trans]","Format$([TranDate],'yyyy mm')='" & [mthYear] & "'")) AS ThisMonth, First(DSum("[GainOrLoss]","[Trans]","Format$([TranDate],'yyyy mm')<='" & [mthYear] & "'")) AS RunningTotal FROM trans GROUPBY Format$([TranDate],"yyyy mm");