12 Month Rolling Sum

G

Guest

In Access 2002, is there a way to create a 12 month rolling total for monthly
information. For example:

Month Monthly total 12-month sum
January 2004 5 5
February 2004 10 15
March 2004 15 30
April 2004 20 50
May 2004 5 55
June 2004 10 65
July 2004 15 80
August 2004 20 100
September 2004 0 100
October 2004 5 105
November 2004 10 115
December 2004 15 130
January 2005 20 145
February 2005 0 135

Any assistance on a rolling sum function based upon date would be
appreciated.
 
D

Duane Hookom

Is there any way that you can store the Month value as two fields being the
Numeric Month and Numeric Year? Storing a text value like "January 2004" is
not too wise since you would need to convert it to a legitimate date or
number in order to create rolling total.
 
J

JohnFol

You could try something using a DSUM

For example
=dsum("MonthlyTotal","MyTable","DateField > " & dateadd("m",-12,[Month]) )
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top