I'm trying to write calculate the moving annual total (Moving Sum Sales in last 12 months by each Representative) for salesByReps in MS-Access.
In SalesByReps table have four columns.
Date EmpID SalesRep Amount
There are four different SalesRep in this table for them I've to evaluate MAT
I have written the following SQL query for this
When i run this query it asks for S1.EmpID and doesn't return the correct Amount
Can anybody help me in figuring out the mistake in the query and eliminate it.
I have attached the copy of SalesByReps Table for better understanding
SELECT Month(S1.Date) AS Month,
Year(S1.Date) AS Year,
FROM SalesByReps AS S2
BETWEEN DateAdd('m', -11,S1.Date) AND S1.Date
AND S2.EmpID = S1.EmpID) AS SUM
FROM SalesByReps AS S1
ORDER BY Year(Date), Month(Date);