Query to show period by period ie year on year (1 Viewer)

[guinness]

Hi guys, I can sort of achieve the result I'm looking for but am sure there is a cleverer nicer way to do it.

I already have a query that sums peoples activities and a form that shows the results using a pivot table. On the pivot table manager types can easly use the week commencing date by month dropdown to obtain weekly/monthly/quarterly and even yearly figures and manager types love this.

What I'm after now is a way to make comparisons and present the results on a similar form. The comparisons could be: this week v last week, this month v last month etc or they could be Jan 2011 v jan 2010, qtr 1 2011 v qtr 1 2010. The form would show the differences in terms of +/- figures and +/- percentages against the comparitive period.

Now I could do this myself by creating a seperate query for every scenario however I'm imagining that access is cleverer than that. I'm thinking there's a way to set up a query with criteria for the start period, criteria for the end period and then display the diff between the two.

Any Ideas much apprecited.

 

[jdraw]

There are examples of Dynamic reports --- collecting parameters and running some code -- here. Lots of other useful stuff as well.

http://www.fontstuff.com/access/acctut19.htm

 

[guinness]

Thanks JD

Through much trial and error I've now managed to create a query that compares the data and allows me to see the difference +/- month on month so now I'm going to use the instructions on dynamic queries that you've sent in order to make it quick for managers etc to run reports.

My idea is that I'll have three main period/period queries (month by month, Qtr by Qtr and year by year. I'm thinking of creating a dynamic report with a dropdown asking you to choose one of the three period/period formats and then a date box for the start report and a date box for the comparison.

It'll take the rest of the week to get it done and running but the idea is that, once these things are built and tested, they become maintenance free.

Thanks again