如何创建一个查询来提供累计总数？

Question

我有一个包含以下列的表：reportDate DATETIME 和损失 CURRENCY，当然还有 ID 列。

如何编写一个查询来返回一个包含损失列的运行总计的表？每个日期都会有多个条目，所以我认为他们需要为每个日期使用 Sum() 。我知道这与 DSum 函数有关，但我仍然迷失在这个函数上。 我认为，它应该看起来像

Month   Losses   Cum
-----   ------   -----
Jan     $3,000   $3,000
Feb     $2,000   $5,000
Mar     $1,500   $6,500

具有非 Access 特定的 sql 语句对我来说是最有帮助的。但所有解决方案均受到赞赏。感谢您的帮助。

Answer 1

我在您问题的编辑历史记录中找到了表和字段名称，因此在此答案中使用了这些名称。您没有提供 record_matYields 示例数据，因此我创建了自己的数据并希望它合适：

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

首先，我创建了 qryMonthlyLosses。以下是 SQL 和输出：

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

我使用第一个查询创建了另一个查询，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

最后，我使用 qryCumulativeLossesByMonth 作为查询中的数据源，该查询将输出转换为匹配您请求的格式。

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

您可能可以使用子查询而不是单独的命名查询将其修改为单个查询。我使用这种逐步的方法是因为我希望它更容易理解。

编辑：我使用 MonthName() 函数返回了全名。如果您需要缩写的月份名称，请将 True 作为第二个参数传递给该函数。其中任何一个都应该有效：

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

Answer 2

此页面看起来很适合您：

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

仅供参考，我之前针对SQL Server写了如下T-SQL：

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

结果：

 key_col     val         cumulated   
 ----------- ----------- ----------- 
           1          10          10 
           2          10          20 
           3          30          50 
           4          10          60 

Answer 3

这将在一个 SQL 中为您完成，而无需使用临时表

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");