3 Month Moving average with three criterias

Looking for a way to calculate a three month average with three criterias (Transaction date, Area and product id).

Current code is: MovingAverage: DAvg("Sales","Table1","Area ='" & [Area] & "' AND Month(format(TransactionDate,'mm/dd/yyyy') <" & (Month(Format([TransactionDate],'mm/dd/yyyy')+2)\3))

Struggeling with the Transaction date as I only receive an error in the query (the data is only monthly and all data is therefor set to 1/1/2015 for Jan and 2/1/2015 for feb).

I would also need product ID to be part of the criteria for the three month moving average.

Any suggestions?  I've attached the test database as well.
3-16-2016-11-30-03-AM.png
3-16-2016-11-30-18-AM.png
TEST--2---4-.mdb
oveloveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
using the domain functions in this type of query is going to be amazingly slow.  Generally, when I need to do something like this, I include the same table in the query twice and use a non-equi join to match three months worth of records from one table to a single record on the other record.

Select T.Area, T.ProductID, T.TransactionDate, T.Sales, SUM(T.Sales) as 3MonAvg
FROM Table1 as T
LEFT JOIN Table1 as TAvg
ON T.Area = TAvg.Area
AND T.ProductID = TAvg.ProductID
AND TAvg.TransactionDate >= DateAdd("m", -3, T.TransactionDate
AND TAvg.TransactionDate < T.TransactionDate
GROUP BY T.Area, T.ProductID, T.TransactionDate, T.Sales

You will not be able to do all of this in the query grid, and if you try to view it in the grid after you write the SQL you will get a message that Access is unable to represent the join in the grid.  So what I usually do is add the tables to the grid, add all of the columns, add the joins on Area, ProductID and Transaction date, then move to the SQL view and tweak the SQL.

HTH
Dale
Rgonzo1971Commented:
Hi,

pls try this SQL
SELECT T1.ProductID, Min(T1.TransactionDate) AS MinOfTransactionDate, T1.Sales, Avg(T2.Sales) AS MovAvg, Count(T2.Sales) AS MonthsInAvg
FROM Table1 AS T1 LEFT JOIN Table1 AS T2 ON T1.ProductID = T2.ProductID
WHERE (((T2.TransactionDate)<=[T1].[TransactionDate] And (T2.TransactionDate)>=DateAdd("m",-2,[T1].[TransactionDate])))
GROUP BY T1.ProductID, T1.Sales;

Open in new window

Regards
oveloveAuthor Commented:
Hi Rgonzo1971,
it works nicely but I'm missing the third criteria "Area". Is there an easy way to include it to the code?
Rgonzo1971Commented:
then try

SELECT T1.Area, T1.ProductID, Min(T1.TransactionDate) AS MinOfTransactionDate, T1.Sales, Avg(T2.Sales) AS MovAvg, Count(T2.Sales) AS MonthsInAvg
FROM Table1 AS T1 LEFT JOIN Table1 AS T2 ON T1.ProductID = T2.ProductID
WHERE (((T2.TransactionDate)<=[T1].[TransactionDate] And (T2.TransactionDate)>=DateAdd("m",-2,[T1].[TransactionDate])))
GROUP BY T1.Area, T1.ProductID, T1.Sales;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
oveloveAuthor Commented:
Thanx, works perfectly
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.