Running month sum

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

 

[Allen Browne]

To list all the items in the total, you would need to write a piece of code
that concatenates the various records into a single output string.

There is an example of how to write such a function in this article:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

 

[John Spencer]

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