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
,但随后需要在 WhereCondition 选项中使用agentID
和分隔符。与子查询方法相比,这似乎需要更多的精力,而且我发现它更容易出错。incurrredDate``DSum
__
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中的示例数据返回您请求的结果。