CODEWITHSUNDEEP
sqlms-access-2013
user3846155
user3846155

Reputation: 17

Creating a Running total using MS Access SQL 2013

I am trying to create a running total using Access SQL I have written the following SQL statement but it is not working. My SQL is not good and I am trying to learn could someone help me with it. It would also be nice if I could see it in design view after I write it. 

SELECT
    SubQrySUMPermCashIN.Date
    ,SubQrySUMPermCashIN.PayeeID
    ,SubQrySUMPermCashIN.PermCashIn
    ,SubQrySUMPermCashIN.Year
    ,Sum(tblTest.PermCashIn) AS RunningTotal
FROM
    SubQrySUMPermCashIN
INNER JOIN
    SubQrySUMPermCashIN AS tblTest
ON
    SubQrySUMPermCashIN.Date = tblTest.Date
WHERE
    ((SubQrySUMPermCashIN.Date)>=[tblTest]![Date])
GROUP BY
    SubQrySUMPermCashIN.Date
    ,SubQrySUMPermCashIN.PayeeID
    ,SubQrySUMPermCashIN.PermCashIn
    ,SubQrySUMPermCashIN.Year;

Upvotes: 0

Views: 90

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I am inclined to do this type of calculation using a correlated subquery:

SELECT spc.Date, spc.PayeeID, spc.PermCashIn, spc.Year,
       (SELECT SUM(sp2.PerCashIn)
        FROM SubQrySUMPermCashIN as spc2
        WHERE spc2.Date <= sp.Date
       ) as RunningTotal
FROM SubQrySUMPermCashIN as spc;

This saves both the join and the outer group by. Note that the use of table aliases also makes the query easier to write and to read.

Upvotes: 1

JohnLBevan
JohnLBevan

Reputation: 24410

I don't have Access to test this, but try the code below:

SELECT
    SubQrySUMPermCashIN.Date
    ,SubQrySUMPermCashIN.PayeeID
    ,SubQrySUMPermCashIN.PermCashIn
    ,SubQrySUMPermCashIN.Year
    ,Sum(tblTest.PermCashIn) AS RunningTotal
FROM
    SubQrySUMPermCashIN
INNER JOIN
    SubQrySUMPermCashIN AS tblTest
ON
    SubQrySUMPermCashIN.Date >= tblTest.Date
GROUP BY
    SubQrySUMPermCashIN.Date
    ,SubQrySUMPermCashIN.PayeeID
    ,SubQrySUMPermCashIN.PermCashIn
    ,SubQrySUMPermCashIN.Year;

NB: your WHERE was contradicting your JOIN ... ON ...; amended it so that the ON contains the correct defintion and the WHERE is not longer required.

Upvotes: 0

Related Questions