Data "snapshot"?

[Swift_74d]

Normally i'll do my best to try using knowledge i have to solve a problem, then ask for help after i've failed misserably. This time i can't even wrap my head around were to begin....

I have a database that is creating monthly claims reports. In addition to these reports i'm being asked to create a "stat" page. This page should be showing current claim counts (by type which there are 12) with thier total claim costs compaired to the same numbers from the year/month prior. Getting the current numbers shouldn't be a problem, however its the keeping track of the prior stats that i can't figure out due to the fact that the numbers in the database now aren't the same as a year prior.

In the past our office has just gone back to the past years books and manually entered the information. Is this how i'm going to have to set it up in the database or is there another easier way i just dont know of?

 

[SydneyGeek]

If your data has dates as part of the records you can use those dates to group records by year (or financial year) and do the stats on those individual years.
You can also compare one year to another once you have those breakdowns.

One way to do this is with a large lookup table of dates. Fields can be along the lines of
ReferenceDate
ReferenceMonth
ReferenceYear
ReferenceQtr
FinYear
FinMonth
FinQtr
WeekOfYear

and anything else you reckon you'll need.
Build the table to cover dates for the next 30 years or so (I tend to do it in Excel and then just import it), and you then have any number of ways to break up dates for reporting.

Denis