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 of the losses column? Each date will have multiple entries so i think they will need use Sum() for each date. I know this has to do with the DSum function but im still lost on this one. It should look something like
Month Losses Cum
----- ------ -----
Jan $3,000 $3,000
Feb $2,000 $5,000
Mar $1,500 $6,500
Having a sql statement that's not Access specific would be the most help to me, I think. But all solutions are appreciated. Thanks for the help.
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:
SELECT
Year(reportDate) AS reportYear,
Month(reportDate) AS reportMonth,
Min(y.reportDate) AS MinOfreportDate,
Sum(y.gainOrLoss) AS SumOfgainOrLoss
FROM record_matYields AS y
GROUP BY
Year(reportDate),
Month(reportDate);
reportYear reportMonth MinOfreportDate SumOfgainOrLoss
2011 12 12/28/2011 $3,000.00
2012 1 1/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
2011 12 12/28/2011 $3,000.00
2012 1 1/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.00
2012 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]
This page looks good for you:
http://support.microsoft.com/kb/290136
FYI, I wrote the following T-SQL against SQL Server before:
create table #a (key_col int, val int)
insert into #a values (1, 10)
insert into #a values (2, 10)
insert into #a values (3, 30)
insert into #a values (4, 10)
select x.key_col,x.val,sum(y.val) as cumulated
from #a x
inner join #a y on
x.key_col >= y.key_col
group by x.key_col,x.val
order by x.key_col,x.val
drop table #a
The result:
key_col val cumulated
----------- ----------- -----------
1 10 10
2 10 20
3 30 50
4 10 60
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 GROUP BY Format$([TranDate],"yyyy mm");
