Rolling 12 Month Report

[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

 

[mcescher]

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

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.

 

[adam.vogg]

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

 

[adam.vogg]

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.

 

[John W. Vinson]

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.

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