sql - Trouble making a running sum in Access query

Question

I'm stuck trying to get a running sum to work in an Access query. I've been playing around with various Dsum expressions, but they all have resulted in errors. Basically, I have two columns, one with a year, one with a count of parts for that year, and I would like the third to be a running sum of the part count over the years.

My SQL for the first two columns looks like this:

    SELECT DatePart("yyyy",[EoL]) AS AYear, Count(EquipmentQuery.Equipment) AS EquipCount
    FROM EquipmentQuery
    GROUP BY DatePart("yyyy",[EoL])
    ORDER BY DatePart("yyyy",[EoL]);

Any suggestions on how to get the third column to work as a running sum? Thanks for the help!

Answer 1

If you create a report, there is a property to calculate a running sum.

If you prefer a query, you can use a subquery to calculate the running sum:

SELECT  DatePart("yyyy",[EoL]) AS AYear
,       Count(eq1.Equipment) AS EquipCount
,       (
        SELECT  Count(eq2.Equipment)
        FROM    EquipmentQuery eq2
        WHERE   DatePart("yyyy",eq2.[EoL]) <= DatePart("yyyy",eq1.[EoL])
        ) AS RunningSuma
FROM    EquipmentQuery AS eq1
GROUP BY 
        DatePart("yyyy",[EoL])
ORDER BY 
        DatePart("yyyy",[EoL]);