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中的示例数据返回您哀求的功效.

（编辑：湖南网）

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