我不是一个很大的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中的示例数据返回您请求的结果.
