Dynamic Report

[cterjesen]

I am trying to create a report in access that will automatically update when I import a new month's data. The report has 13 months (columns) of rolling data. For example, the last report had July 02 through July 03 as the columns. This month, when I import the new data into access for August I want the report to show August 02 through August 03 data.

Is this possible?

Thanks

 

[SydneyGeek]

Yes, but the headings in the Report will need to be textboxes rather than labels. Presumably you have logic on the underlying query that advances the dataset?
For the headings try this: (I'm assuming that the set is -12, -11, -10... -1 months from some trigger date and a Total).
For the first heading TextBox, the ControlSource should be:
=DATEADD("m",Your_Trigger_Date,-12) "m" gives you months. Check the syntax of the function in case Your_Trigger_Date and -12 are round the wrong way -- i'm not in front of Access at the moment.
Repeat for the other headings, then adjust the format to display the headings. Try "mmm yy" for the date format, then play with the fonts.

Hope that helps.

 

[mdmilner]

I'll have to try this soon. This seems like a better solution than one I've done. One thing to add - try using a crosstab query as the recordsource for the report.