Calculate rolling year total

[douggy]

I need to create running totals that run from 1 year to the next by month.

ie the total number of attendees from Jan 03 to Jan 04, Feb 03 to Feb 04, Mar 03 to Mar 04 etc etc

I am then going to put it in a graph. I'm not sure where to starr on this. Can anyone give me a hand.

Thanks
Mark

 

[M8KWR]

Do you need a graph that shows just Jan 03 to Jan 04, and then a grapth that shows Feb 03 to Feb 04.

Or a graph that shows Jan Feb March 03 - 04 etc.

If you could give me the tables and the field name of the date field and the any other you feel you would need on the report.

 

[douggy]

Thanks for your response. Perhaps this will give more detail:

I need a query to sum the Number Attendees for each month in rolling years. ie sep 2003 to sep 2004 (total=16840), oct 2003 to oct 2004 (total=18582)etc

It all needs to be in one query and then graph. Here is the data sample I have.


Many thanks again. Mark

DATA SAMPLE:

MonthNo Month Year NumberAttendees
09 Sep 2003 113
10 Oct 2003 884
11 Nov 2003 946
12 Dec 2003 659
01 Jan 2004 746
02 Feb 2004 1086
03 Mar 2004 1964
04 Apr 2004 1797
05 May 2004 1542
06 Jun 2004 1776
07 Jul 2004 1824
08 Aug 2004 1722
09 Sep 2004 1781
10 Oct 2004 1855
11 Nov 2004 2169
12 Dec 2004 1574
01 Jan 2005 1267
02 Feb 2005 251
03 Mar 2005 17

 

[M8KWR]

Firstly it would be better to have a date field instead of 3 sperate columns.

I'm still thinking about the best way to achieve this and i am struggling.

I will get back to you when i can figure out an answer...

 

[PHV]

A starting point (typed, untested):
SELECT A.MonthNo,A.Month,A.Year,Sum(NumberAttendees) AS TotalAttendees
FROM yourTable A INNER JOIN yourTable B
ON (100*B.Year+B.MonthNo) Between (100*A.Year+A.MonthNo) And (100*(A.Year+1)+A.MonthNo)
GROUP BY A.MonthNo,A.Month,A.Year
HAVING Count(*)=12

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244