MS Access 2010在查询中的运行总计 [英] MS Access 2010 Running Total in Query

问题描述

我不是Access的忠实拥护者，但是对于这个项目，我被要求创建一个Access数据库.我为其他数据库类型创建了类似的东西，所以并不太困难.我大部分都想通了，但总的运行量让我大吃一惊.

I am not a big Access fan but for this project I was asked to create an Access database. I created something similar for other database types so it wasn't too difficult. I have most of it figured out but the running totals are racking my head.

我有下表:表名attendanceView

查询:SELECT * FROM attendanceView

======================================================================================
agentID    |     incurrredDate     |   points    |    OneFallOff     |    TwoFallOff
======================================================================================
chtall     |       10/7/2013       |     2       |       2           |        2
chtall     |       10/15/2013      |     2       |       2           |        2
chtall     |       11/26/2013      |     2       |       2           |        2
chtall     |       12/17/2013      |     2       |       2           |        2
vimunson   |       7/22/2013       |     2       |       2           |        2
vimunson   |       7/29/2013       |     2       |       1           |        1
vimunson   |       12/6/2013       |     1       |       1           |        1

此查询执行查找OneFallOff和TwoFallOff的值所需的操作.但是，我需要找到一种方法来为每个agentID运行总计TwoFallOff.例如，chtall有四个记录，如下所示:

This query does what it needs to do to find the values for OneFallOff and TwoFallOff. However I need to find a way to run a running total of TwoFallOff for each agentID. For example chtall has four records, see below, this what it should look like:

==================================================================================================
agentID    |     incurrredDate     |   points    |    OneFallOff     |    TwoFallOff     |   total
==================================================================================================
chtall     |       10/7/2013       |     2       |       2           |        2         |    2
chtall     |       10/15/2013      |     2       |       2           |        2         |    4
chtall     |       11/26/2013      |     2       |       2           |        2         |    6
chtall     |       12/17/2013      |     2       |       2           |        2         |    8
vimunson   |       7/22/2013       |     2       |       2           |        2         |    2
vimunson   |       7/29/2013       |     2       |       1           |        1         |    3
vimunson   |       12/6/2013       |     1       |       1           |        1         |    4

我尝试了DSUM()，该命令不起作用，或者我使用的是错误的.如果表中的内容不清楚，则在代理更改时，total列将重置为0.

I've tried DSUM() which did not work, or maybe I was using it wrong. In case it isn't clear in the table the total column resets back to 0 when the agent changes.

推荐答案

您可以通过相关子查询获得所需的内容.

You can get what you want with a correlated subquery.

SELECT
    a1.agentID,
    a1.incurrredDate,
    a1.points,
    a1.OneFallOff,
    a1.TwoFallOff
    (
        SELECT Sum(a2.TwoFallOff)
        FROM attendanceView AS a2
        WHERE
                a2.agentID = a1.agentID
            AND a2.incurrredDate <= a1.incurrredDate
    ) AS total
FROM attendanceView AS a1;

您也可以使用DSum来做到这一点，但是随后您需要在DSum WhereCondition 选项中使用带有agentID和incurrredDate的定界符.与子查询方法相比，这似乎需要更多的精力，而且我发现它更容易出错.

You could also do it with DSum, but then you need to use delimiters with agentID and incurrredDate in the DSum WhereCondition option. It seems like more effort, and I found it more error-prone, than the subquery approach.

SELECT
    a.agentID,
    a.incurrredDate,
    a.points,
    a.OneFallOff,
    a.TwoFallOff,
    DSum
        (
            "TwoFallOff", "attendanceView",
            "agentID = '" & a.agentID & "' " &
            "AND incurrredDate <= " & 
            Format(a.incurrredDate, "\#yyyy-m-d\#")
        ) AS total
FROM attendanceView AS a;

两个查询都使用Access 2007中的示例数据返回请求的结果.

Both queries return your requested results using your sample data in Access 2007.

这篇关于MS Access 2010在查询中的运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！