I have a query which gives me monthly figures, what I want to do is get a running annual average.
This gives me a monthly figure for a report but I would like the ability to graph this and therefore need to add a field which includes all the values for the last twelve months divided by twelve, this would appear every month.
My result would be a graph showing a line for each month's total and another line for each month's annual average (the divide by 12 making this a monthly average over the last twelve months).
Any help would be greatly appreciated.
Code:
SELECT Sum(tblOrders.Value) AS SumOfValue, Format([tblOrders]![Date],"yyyy mm") AS Expr1
FROM tblOrders
GROUP BY Format([tblOrders]![Date],"yyyy mm")
ORDER BY Format([tblOrders]![Date],"yyyy mm") DESC;
My result would be a graph showing a line for each month's total and another line for each month's annual average (the divide by 12 making this a monthly average over the last twelve months).
Any help would be greatly appreciated.