sql - MS Access 2010 Running Total in Query

Question

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.

I have the table below: table name attendanceView

Query: 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

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

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.

Answer 1

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;

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;

Both queries return your requested results using your sample data in Access 2007.