Rolling 12 Month Report

  • Thread starter Thread starter adam.vogg
  • Start date Start date
A

adam.vogg

I need to create a report that shows Rolling 12 month sums over a
range of months.

i.e. the range jan07 - dec07 would need to generate rolling sums
where
jan07 = sum(jan07 - feb06)
feb07 = sum(feb07 - mar06)
....
....
dec07 = sum(dec07 - jan07)

eventually i would like these rolling sums in a graph.

I have created the VBA and the query which creates a table with all of
the data I need, but I cant figure out how to get these sums in the
report in an efficient manner.

any ideas? I am comfortable with VBA.

Thanks,

Adam
 
I need to create a report that shows Rolling 12 month sums over a
range of months.

i.e.  the range jan07 - dec07 would need to generate rolling sums
where
jan07 = sum(jan07 - feb06)
feb07 = sum(feb07 - mar06)
...
...
dec07 = sum(dec07 - jan07)

eventually i would like these rolling sums in a graph.

I have created the VBA and the query which creates a table with all of
the data I need, but I cant figure out how to get these sums in the
report in an efficient manner.

any ideas?  I am comfortable with VBA.

Thanks,

Adam
Click to expand...

How is your table organized? Are jan07, feb07, etc... column headings
or are they data in a column that tells the date for the data in that
row?

I would shy away from using them as column names, and I would probably
use a real date instead of text like "jan07". Access has a lot of
function for dealing with dates that are real.

Let us know how you've set up the table, and we can provide an answer.

Chris M.
 
column header is Fiscal Period, and the format of the data is 200801,
200802, etc.

I can convert those to actual date formats if you'd like, I often use
the DateSerial function since these databases store dates in YYYYMMDD
integer formats....
 
to expand, another column header is PieceCount. So i would want a
rolling 12 month sum on the pieces. I was thinking about using a
cross tab to get my sums per fiscal period. but then i think i will
be forced to use some VBA to get the correct rolling 12 month sums
that correspond to each fiscal period.
 
to expand, another column header is PieceCount. So i would want a
rolling 12 month sum on the pieces. I was thinking about using a
cross tab to get my sums per fiscal period. but then i think i will
be forced to use some VBA to get the correct rolling 12 month sums
that correspond to each fiscal period.
Click to expand...

Nope... not needed. There's an option to include a row sum in the crosstab
query wizard. That should do the job for you; just use a criterion
 
Back
Top