[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2794
  • Last Modified:

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
0
kewal12
Asked:
kewal12
  • 3
  • 3
  • 2
  • +2
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now