Rolling 3 Month sums

[michaeljohnh]

I am looking to track the amount of tickets each agent opens per month. I am trying to make a report that looks at the current month and displays by agent their monthly totals for the current month, previous month and two months ago in a total of four columns (one for agent, one for each month).



I have a table named Tickets with the relevant fields OpenDate and User. I have technically been able to accomplish this manually by using a crosstab query (named Tickets_Crosstab2) which displays all twelve months and then creating a report with the record source being the query. I then drag and drop the 3 controls corresponding to the three months i want. However, doing it this way, every months I will need to go back and change which 3 fields are being used.

So what I need to know is :
1.What is the calculation I need to determine the three months to use (I presume it would utilize the Date() function)?
2.Does the calculation go into the crosstab query or the report, and where specifically?

[NoellaG]

Hi,

* you can use the datediff function to look for the months you want to use
* in the report you can first group by "user" and then group on "OpenDate" by month and use the Count function in the groups footer to count the tickets.

gr
NG