Running month sum

  • Thread starter Thread starter ArthurAccroc
  • Start date Start date
A

ArthurAccroc

I have a table with the following fields:
Product Date(month) TotalSales
product1 200503 50
......................
product1 200603 35
product1 200604 20
product1 200605 40
product1 200606 65
product2 ......

I would like to create a query to display something like:
Product Date(month) TotalSales (last 3 months)
product1 200604 xxx (20+35+.....)
product1 200605 95 (40+20+35)
product1 200606 125 (65+40+20)

Any suggestions would be greatly appreciated!
 
What type of field is Date. Is it numeric or text? Assuming it is text,
you will have to do some manipulation to get the month that is two months
prior. Something like the following.

Format(DateSerial(Left([YourTable].[Date],4),Right([YourTable].[Date],2)-2,1),"yyyymm")

You should end up with SQL that looks something like the following

SELECT PRODUCT
, [Date]
, (SELECT Sum(TotalSales)
FROM YourTable as Temp
WHERE Temp.[Date] Between
Format(DateSerial(Left([YourTable].[Date],4),Right([YourTable].[Date],2)-2,1),"yyyymm")
AndYourTable.[Date]
) as 3MonthTotal
FROM Your Table

If you are doing this in the query grid add in your fields and then make a
calculated field that is like
Field: 3MonthTotal: (SELECT Sum(TotalSales) FROM YourTable as TempWHERE
Temp.[Date] Between
Format(DateSerial(Left([YourTable].[Date],4),Right([YourTable].[Date],2)-2,1),"yyyymm")
AndYourTable.[Date] )
 
Back
Top