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参与视图

======================================================================================
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<中使用带有 agentID 和 incurrredDate 的分隔符/code> WhereCondition 选项.与子查询方法相比，这似乎更费力，而且我发现它更容易出错.

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屋！