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.
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.