Sum only last twelve months

[jayr911118]

Hello,

I would like to keep a table that has twelve months data in it with the months as fields:

January February ... December

The catch is once a month I want to append the previous month and lop off the month furthest back. Ultimatley I want a report that will sum the twelve months in the table. My question is how do I have a report read the field labels to sum only the last twleve months. Can anyone give me clue on how to go about this?

Thanks in advance.

 

[Andrew Fergus]

Hi
I'm not sure why you want to have the 12 months in 12 different fields. Such a structure is going to make it very difficult to design queries, forms and reports that will stand the test of time. A data structure that will be easier to handle and use will be one where the month (or date) is entered into one field such that the data would look like this instead :

Month/Date Value
Jan
Feb
..
Dec

If you want to produce reports etc that look like :

Jan Feb Mar ... Dec

then this can be accomplished very easily (with crosstab queries and reports) using the structure I suggested. As you have already identified, it will be very difficult having the 12 months in different columns. Also, summing the last 12 months will also be very easy (see some of the other recent questions on this board concerning dates and reports). Lastly, you won't need to lop off the 13th month if you include the year (as part of the date) which will give you a history that you can use in comparison reports.

HTH, Andrew.