How to include Double Moving Average in direct SQL in one query?

Question

EDIT: I need to do this in ACCESS.

I am a virgin SQL and really appreciate any magical help!

For a simple 12-month forecast, I use a 12-month double moving average . I managed to get the Single Moving Average through query 1 (see below). Based on the table created by Query 1, I wrote another query (Query 2) to get the Double Moving Average.

So my current process requires two queries. My efforts so far to combine these two steps into one request have not been successful.

My question is: is there a way to calculate the Double Moving Average in a single query?

QUERY 1 - for one moving average:

SELECT A.*, IIf([A].[VOL]>0, (SELECT AVG(B.[VOL]) FROM [Turnover] as B WHERE (B.Code = A.Code) AND (B.YM Between A.YM - 1 AND A.YM - ([12] * 31))), (SELECT AVG(B.[VOL]) FROM [Turnover] as B WHERE (B.Code = A.Code) AND (B.YM Between Now() - 31 AND A.YM - ([12] * 31)))) AS [Mvg Avg 1], INTO [Model 12m] FROM [Turnover] AS A; 

QUERY 2 - Double Moving Average (currently this applies to QUERY 1):

  SELECT A.*, IIf([A].[Mvg Avg 1]>0,(SELECT AVG(B.[Mvg Avg 1]) FROM [Model 12m] as B WHERE (B.Code = A.Code) AND (B.YM Between A.YM - 1 AND A.YM - ([12] * 31))),(SELECT AVG(B.[Mvg Avg 1]) FROM [Model 12m] as B WHERE (B.Code = A.Code) AND (B.YM Between Now() - 31 AND A.YM - ([12] * 31)))) AS [2 Mvg Avg], INTO [Model 12m - 2MA] FROM [Model 12m] AS A; 

Answer 1

My question is: is there a way to calculate the double moving average in a single query?

Since you are using Microsoft Access, there should not be a need to do this. Save the first query as a new query. (For people who do not use MS Access, saving the SQL statement as a new query is equivalent to the SQL CREATE VIEW ... statement.) Then use the second query as is or save it as another new query.

MS Access is really good at optimizing queries based on queries (views based on views).