Calculate Month Variance on Crosstab report 1

[afeind]

I have a cross tab query that I am putting on a report. The months 1,2,3..12 print on the top. I need to calculate the difference in month 1 between 2005 & 2004, then month 2, etc. Here is my crosstab query below:

TRANSFORM Sum([sales query 2004-2005].Quantity) AS SumOfQuantity
SELECT [sales query 2004-2005].PlantID, [sales query 2004-2005].ProductID, [sales query 2004-2005].Year, Sum([sales query 2004-2005].Quantity) AS [Total Of Quantity]
FROM [sales query 2004-2005]
GROUP BY [sales query 2004-2005].PlantID, [sales query 2004-2005].ProductID, [sales query 2004-2005].Year
PIVOT [sales query 2004-2005].Month;

The sum function works great but I really need the difference in 2005-2004. Thanks for any help.

 

[dhookom]

Can you just put this in a report and do the math there?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[afeind]

This is what I am trying to do. I can get the xtab in a report and do a sum on 2004 and 2005 but I cannot figure out how to do the difference. I need to take the total for month 1 year 2005 and subtract month 1 year 2004, then month 2, etc. If the sales for the product was 50 for January 2005 and 25 for January 2004 then I want to show a positive 25 for the month over last year. I hope this makes sense.

Thanks for any help.

Adam

 

[dhookom]

You can try control sources in a Footer like:
=Sum(Abs([Year]=2005)*[Month1]) - Sum(Abs([Year]=2004)*[Month1])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[afeind]

This works great!

Thanks

Adam