Updating monthly data with report that always displays the 'last 12 months' in order

[jlk]

I have a report that shows employees names as a grouping, and in the detail section displays data from a crosstab which breaks up departments and sales totals per month. The column headings are all the last 12 months in order and row headings of departments.

What I'm stuck on is every month I have to manually change the data source for the text boxes to move the months over. I also have to change the labels to match.

So the first text box was Oct 2012, second Nov 2012 and so on. I then change the first box to Nov 2012 second to Dec 2012 and so on.



I am looking for a solution where maybe I can put vba in the datasource for the first box to collect the data from the specific month that I wish to start on the table. The queries that the report is collecting the data from updates each month to reflect the correct months for the report. However when running the report it presents with the error due to not recognising eg: Oct 2011, as this month was in the last report but no longer in the current query as it now has Nov 2012 (being 12 months of data to date).

If anyone can assist or need more info to assist please let me know.

Thanks in advance

[June7]

Stabilizing a crosstab to run perpetually in a report can be tricky, especially when pivoting on date data and the report needs to be dynamic for progressive reporting periods. Sometimes the solution is a crosstab that isn't a pivot query. The crosstab fields are created by expressions. No VBA is involved in this approach. Start with this sample data:

EmpID	Dept	SaleDate	Sales
11	Mens	2/15/2012	200
11	Womens	2/15/2012	300
12	Mens	2/15/2012	100
12	Children	3/15/2012	50
11	Children	4/15/2012	500
12	Mens	5/15/2012	400

And this query:
SELECT EmpID, Dept, Sum(IIf(Format([SaleDate],"mmmyyyy")=Format([enter date],"mmmyyyy"),[Sales],0)) AS MoYr1, Sum(IIf(Format([SaleDate],"mmmyyyy")=Format(DateAdd("m",1,[enter date]),"mmmyyyy"),[Sales],0)) AS MoYr2
FROM Table1
GROUP BY EmpID, Dept;

[enter date] is an input parameter prompt but could instead be a reference to a textbox on a form. Then labels on the report could be textboxes with an expression to calculate the text to display as a caption. The expressions could reference the form textbox:
=Format(Forms!formname!textboxname, "mmm" & "(" & Year(Forms!formname!textboxname) & ")")
=Format(DateAdd("m",1,Forms!formname!textboxname), "mmm" & "(" & Year(DateAdd("m",1,Forms!formname!textboxname)) & ")")