我如何创建一个查询，该查询将给我一个累计的总数? [英] How do i create a query that will give me a cumulative total?

问题描述

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

I have a table with the following columns: reportDate DATETIME and losses CURRENCY and of course the ID column.

如何编写一个查询，该查询将返回损失列的运行总计表?每个日期将有多个条目，所以我认为他们将需要为每个日期使用Sum().我知道这与DSum函数有关，但是我仍然对这一功能迷失了.看起来应该像

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

我认为，拥有一条非Access特定的sql语句对我来说最有帮助.但是所有解决方案都值得赞赏.感谢您的帮助.

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.

推荐答案

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

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

首先，我创建了 qryMonthlyLosses .这是SQL和输出:

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

我使用第一个查询创建了另一个 qryCumulativeLossesByMonth :

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

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

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.

编辑:我使用MonthName()函数返回了全名.如果您需要缩写的月份名称，请将True作为第二个参数传递给该函数.这些都应该起作用:

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]

这篇关于我如何创建一个查询，该查询将给我一个累计的总数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！