asked on

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

peter57r

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.

dandraka

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.

Vishnukumar

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);

GRayL

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.

kewal12

ASKER

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.

GRayL

Show us a few lines of what you want, not what you get.

kewal12

ASKER

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

Vishnukumar

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)

ASKER CERTIFIED SOLUTION

kewal12

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

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