Cuummulative Totals By Month (1 Viewer)

[cabber]

I would like to create a query that will add the previous months figure as a cummulative total per month ie
Jan 2250
Feb 3000 This would be 5250
March 2000 This would be 7250
and so on

Any help greatly appriciated

Thank

 

[Ranman256]

you can do it easily in a report.
set the query to the report, add another text box for Amt (this will be the running total amt)
set property to RUNNING SUM =OVER ALL, (or over group)
easy.

not so with query alone.

 

[plog]

Can you post sample data from the table this will be built on? Include table and field names and enough data to cover all cases.

 

[arnelgp]

replace the tablename, fieldnames in this query with the
name of table,field you have:

SELECT
    Format([dateField],"mmm yyyy") AS MonthYear,
    DSum("amountField","yourTableName",
        "Format(datefield,'yyyymm') <= '" & Format([dateField],"yyyymm") &
        "' And Year(dateField)=" & Year([dateField])) AS RunningTotal
FROM yourTableName
GROUP BY
    Format([dateField],"mmm yyyy"),
    DSum("amountField","yourTableName","Format(datefield,'yyyymm') <= '" & Format([dateField],"yyyymm") &
    "' And Year(dateField)=" & Year([dateField])), Format([datefield],"yyyymm")
ORDER BY Format([datefield],"yyyymm");

 

[cabber]

After a few errors, managed to get it working

Thanks