Query Design Help - YTD Monthly Comparisons/Budget Statement

[warriorwoman]

I desperately need help with query design and any pointers would be greatly appreciated. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I currently have multiple monthly databases storing YTD medical expense data – expenditure by purchaser, provider and treatment method.<o></o>
<o> </o>
I now want to create a consolidated database so that I can compare performance trends on a monthly basis.<o></o>
<o> </o>
I’ve created an append query from my individual monthly databases to create a mega table with the following fields:<o></o>
<o> </o>
Purchaser<o></o>
Provider<o></o>
Point of Delivery<o></o>
Specialty<o></o>
Treatment<o></o>
Month<o></o>
YTD Plan<o></o>
YTD Actual<o></o>
YTD Variance (YTD Plan – YTD Actual)<o></o>
<o> </o>
Each individual months data is YTD rather than in month actual and I need to find either a way to calculate the in month position or find a way to display YTD data in a meaningful way.<o></o>
I currently have 3 months data available and M3 is the latest month.<o></o>
I had thought to create a query (presumably multiple queries reqd) that will give the following output:<o></o>
<o> </o>
Purchaser<o></o>
Provider<o></o>
Point of Delivery<o></o>
Specialty<o></o>
Treatment<o></o>
M3 YTD Plan<o></o>
M1 Variance<o></o>
M2 Variance<o></o>
M3 Variance<o></o>
M3 Variance % (M3 as a % of M3 YTD Plan)<o></o>
In Month Movement (Perhaps M3 Variance – M2 Variance)<o></o>
<o> </o>
I’ve got as far as creating a crosstab with the M1, M2 and M3 Variances but I can’t think how to show the latest months plan against all these (currently M3) and I have the feeling I may be creating a monster.<o></o>
<o> </o>
Can anyone help?<o></o>