Running Average in Query (1 Viewer)

Tieval · Oct 11, 2018

I have a query which gives me monthly figures, what I want to do is get a running annual average.

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;

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.

plog · Oct 11, 2018

First, I hope that's an example table, because both your fields are reserved words (https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe). Those should never be used for names because they make coding and querying harder--this is going to require both.

Now, as you've explained it, I would use the DSUM function for this (https://www.techonthenet.com/access/functions/domain/dsum.php). It allows you to sum up data in a source and set criteria. You would set the criteria to include just the values from the 12 months prior to the Date value of each record--then divide the whole thing by 12.

Tieval · Oct 12, 2018

The original query does this anyway and produces a set of records such as the following:

2018 01 125.00
2018 02 700.00
2018 03 420.00

My problem is working out how to average the last twelve values from this and also how to avoid an error when there are not enough values as below:

201701 198
201702 430
201703 690
201704 146
201705 759
201706 354
201707 376
201708 205
201709 809
201710 365
201711 578
201712 544 454.50
201801 125 448.42
201802 700 470.92
201803 420 448.42

As can be seen, the first eleven entries cannot have an average but after that you can take the sum of the last twelve and divide by twelve and use this to graph a trend line, easy in excel!

Any thoughts would be greatly appreciated.

And just for good measure:

Code:

SELECT Sum(tblOrders.OValue) AS SumOfOValue, Format([tblOrders]![ODate],"yyyy mm") AS Expr1
FROM tblOrders
GROUP BY Format([tblOrders]![ODate],"yyyy mm")
ORDER BY Format([tblOrders]![ODate],"yyyy mm") DESC;

CJ_London · Oct 12, 2018

you need a second query to join this one to itself. Assuming your query is called QSum, then something like this

Code:

SELECT Q1.Expr1, Q1.SumOfOValue, Avg(Q2.SumOfOValue) As MovingAvg
FROM QSum Q1 INNER JOIN QSum Q2 ON Q2.Expr1 BETWEEN left(Q1.Expr1,4)-1 & format(right(q1.Expr1,2)+1,"00") AND Q1.Expr1
GROUP BY Q1.Expr1, Q1.SumOfOValue

you might want to include a datetype field in your original query to make the comparison easier and probably faster.

This will include averages for the first 11 months. to exclude you would need to use an iif function on the count of Q2 - not tested but perhaps something like

iif(count(Q2.Expr1)=12,Avg(Q2.SumOfOValue)) AS MovingAvg

Tieval · Oct 12, 2018

Many thanks for this CJ but I am getting an error: Between operator without And in query expression..............

CJ_London · Oct 12, 2018

can't help withought knowing exactly what you have - my example has an AND in it

Tieval · Oct 12, 2018

It may just be me being thick!

SELECT QSum1.Expr1, QSum1.SumOfOValue, Avg(QSum2.SumOfOValue) As MovingAvg
FROM QSum1 INNER JOIN QSum2 ON QSum2.Expr1 BETWEEN left(QSum1.Expr1,4)-1 & format(right(QSum1.Expr1,2)+1,"00") AND QSum1.Expr1
GROUP BY QSum1.Expr1, QSum1.SumOfOValue

I have also attached a mock-up.

CJ_London · Oct 12, 2018

you have not used as I suggested

FROM QSum Q1 INNER JOIN QSum Q2 ON Q2.Expr1 BETWEEN left(Q1.Expr1

Tieval · Oct 12, 2018

I think this is where I am missing something, attached is the original with the table and QSum query. If I try to create a query as follows, it refuses to save due to the Between issue.

SELECT Q1.Expr1, Q1.SumOfOValue, Avg(Q2.SumOfOValue) As MovingAvg
FROM QSum Q1 INNER JOIN QSum Q2 ON Q2.Expr1 BETWEEN left(Q1.Expr1,4)-1 & format(right(q1.Expr1,2)+1,"00") AND Q1.Expr1
GROUP BY Q1.Expr1, Q1.SumOfOValue

CJ_London · Oct 12, 2018

seems the linking is too complex due to your year/month format

use a cartesian query instead - and you don't need to repeat the query

Code:

SELECT Q1.Expr1, Q1.SumOfOValue, IIf(Count([Q2].[Expr1])=12,Avg([Q2].[SumOfOValue])) AS MovingAvg
FROM QSum AS Q1, QSum AS Q2
WHERE (((Q2.Expr1) Between Left([Q1].[Expr1],4)-"1" & " " & Format(Right([q1].[Expr1],2)+1,"00") And [Q1].[Expr1]))
GROUP BY Q1.Expr1, Q1.SumOfOValue

For large datasets, the cartesian will be slower, but suspect that won't be an issue here

Tieval · Oct 12, 2018

Hi CJ,
Many thanks for this, works perfectly, I just need to work out why now (for my personal education).

Running Average in Query (1 Viewer)

Tieval

Still Clueless

plog

Banishment Pending

Tieval

Still Clueless

CJ_London

Super Moderator

Tieval

Still Clueless

CJ_London

Super Moderator

Tieval

Still Clueless

Attachments

CJ_London

Super Moderator

Tieval

Still Clueless

Attachments

CJ_London

Super Moderator

Tieval

Still Clueless

Similar threads

Users who are viewing this thread