How to calculate Moving Annual Total (MAT) in Ms-Access

Hi
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

Regards
Kewel
SELECT Month(S1.Date) AS Month, 
Year(S1.Date) AS Year,
S1.SalesRep,
(SELECT SUM(S2.Amount) 
FROM SalesByReps AS S2 
WHERE S2.Date 
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);

Open in new window

SalesByReps.txt
kewal12Asked:
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.

peter57rCommented:
I can't explain why you get the parameter prompt.  If I put your data into excel and then run your Access query on the linked table, it runs ok.

But your sql is going to produce one output record for each record on SaleByReps, so you will have many records for each month/EmpId combination.  What you are creating a is a moving sum based on each transaction date whereas I originally read the Q as trying to produce a moving sum by month.
I also think you should be using -12 not -11 for your sum if you want a year's data included.
0
dandrakaCommented:
try using brackets around your field names...that could solve the parameter issue
also if you have a field that appears in 2 tables, that could also cause problems...better to refer to it in the Table.[field] format.
0
VishnukumarCommented:
please try this.
please change the condition saldate > '2009-10-10  as per yours.
select month(s1.saldate),year(s1.saldate),s1.empid,s1.salesrep,(select sum(amount) from salesbyrep s2 where s2.salDate < s1.salDate) from salesbyrep
s1 where month(s1.saldate) in (select distinct month(saldate) from salesbyrep where saldate > '2009-10-10 order by saldate);
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

GRayLCommented:
Using -11 makes the start date 2009-04-30 to today, 2010-03-31.  Is that what you wanted?  In my book, 'for the last year from today' would start at DateAdd("yyyy",-1,Date())+1, or 2009-04-01 to 2010-03-31.
0
kewal12Author Commented:
Hi Vishnu
I changed the query according to your suggestion
but when I tried to run query it pop-up message that
query is too complex to run.
Anyway, Now I have created a new query to sum up
sales amount by sales Reps during each month.
I have used following code to create a new query 'SalesRep'

SELECT Month(S1.SalDate) AS [Month],
Year(S1.SalDate) AS [Year], S1.SalesRep,
SUM(S1.Amount) AS Total
FROM SalesByReps AS S1
GROUP BY Month(S1.SalDate),
Year(S1.SalDate), S1.SalesRep, S1.EmpID
ORDER BY Year(SalDate), Month(SalDate);

After running this query i'm getting result as following

Month Year  SalesRep        Total
1      2009      -RNSW/ACT      23113.34
1      2009      -RQLD              11159.21
1      2009      -RSA               5421.79
1      2009      -RVIC              10136.85
2      2009      -RNSW/ACT      18968.74
2      2009      -RQLD              7208.39
2      2009      -RSA                      6897.86
2      2009      -RVIC              5855.19
3      2009      -RNSW/ACT      14029.27
3      2009      -RQLD              7175.64
3      2009      -RSA                      11119.43
3      2009      -RVIC              6677.64
4      2009      -RNSW/ACT      23030.8
4      2009      -RQLD              7385.21
4      2009      -RSA               9201.48
4      2009      -RVIC              6414.75
5      2009      -RNSW/ACT      13760.04
5      2009      -RQLD              10274.22
5      2009      -RSA                      4377.94
5      2009      -RVIC              12467
6      2009      -RNSW/ACT      23172.61
##########################
so on up-to current month.
In this problem is that month field is treated as number not as date.
So please, Anybody can tell me that what I need to do to Add the Total amount of
one year for each SalesRep, like when Year= 2010 Month= 2
Total = Sum( Total of 12 months including 2nd month)  i.e. Sum of Total from 3rd month of 2009 to 2nd month 2010.

 
0
GRayLCommented:
Show us a few lines of what you want, not what you get.
0
kewal12Author Commented:
Is it possible to calculate Moving monthly total in each month
when month and year columns are in Number note Date.
I have attached the file. in it have the monthly total
for which i'm trying to calculation.
Can I use for loop in access.
SalesRep.xlsx
0
VishnukumarCommented:
My MS-ACCESS crashed.  i not able to test the query...

please try to sum the amount with previous date in sum(amount)

(select sum(amount) from salesbyrep s2 where s2.salDate < s1.salDate)
0
kewal12Author Commented:
Thanks everyone for help
I've found the solution for calculating MAT
Select t1.Month, t1.Year, t1.SalesRep, sum(t2.Total) as MAT
from SalesRep as t1
inner join SalesRep as t2
on t1.SalesRep = t2.SalesRep
and (t1.Year*12+t1.Month 
  - t2.Year*12-t2.Month) between 0 and 11
group by t1.Month, t1.Year, t1.SalesRep
having count(*) = 12
order by  t1.Year,t1.Month;

Open in new window

0

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
GRayLCommented:
so now you see how do  it using just SalesDate?  In other words you do not need a table with Date, Year, Month - just Date
0
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
Oracle Database

From novice to tech pro — start learning today.