sql – MS Access 2010在查询中运行总计

我不是一个很大的Access粉丝,但是对于这个项目,我被要求创建一个Access数据库.我为其他数据库类型创建了类似的东西,所以它并不太难.我已经弄明白了,但是跑步总数让我头疼.

我有下表：table name 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有四条记录,见下文,它应该是这样的：

==================================================================================================
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()无效,或者说我错了.如果表中不清楚,则当代理更改时,总列将重置为0.

解决方法

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的分隔符.似乎更多的努力,我发现它比子查询方法更容易出错.

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中的示例数据返回您请求的结果.

（编辑：核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!