Crosstab query challenge

[Kevin Labore]

I am having a challenge with a query/report in order to get the results I
want
I believe I may need a query/subquery but have not done one before now.
I am looking for some guidence how to accomplish it

query1
TRANSFORM Sum([DeptsSales Query].[DeptSales]) AS SumOfDeptSales
SELECT DatePart("m",[deptdate],1,1) AS [Month], [DeptsSales
Query].[DeptDesc], Avg([DeptsSales Query].[DeptSales]) AS [AVG Of
DeptSales], Sum([DeptsSales Query].[DeptSales]) AS TotalDeptSales
FROM [DeptsSales Query]
GROUP BY DatePart("m",[deptdate],1,1), [DeptsSales Query].[DeptDesc]
ORDER BY DatePart("m",[deptdate],1,1), [DeptsSales Query].[DeptDesc],
DatePart("yyyy",[deptdate],1,1) DESC
PIVOT DatePart("yyyy",[deptdate],1,1);

Query1 works fine I use a report to calculate the monthly totals, change in
$ and % of increase over previous year as well as Dept% and change over
previous year. The report has grouping so it groups by Months,Dept. and the
years are in the cols as well as change between the 2 years
So the report1 works fine when I group by Month/Dept since the the report
can calcuate the monthly total $ for all depts and then caculate each Dept%

The report2 does not work for dept% when I group by Dept/Month. It still
works for the $ amount in change over previous year.
The problem is I need query to figure the Monthly totals(for all depts.) so
that I can determine the % for each dept for each period(month/year)
(Report2) is what I need help with

Query2 (Below) accomplishes this but then I cant build the report

Query2
TRANSFORM Sum([DeptsSales Query].[DeptSales]) AS SumOfDeptSales
SELECT DatePart("m",[deptdate],1,1) AS [Month],
DatePart("yyyy",[deptdate],1,1) AS [Year], Sum([DeptsSales
Query].[DeptSales]) AS [TOT Of DeptSales]
FROM [DeptsSales Query]
GROUP BY DatePart("m",[deptdate],1,1), DatePart("yyyy",[deptdate],1,1)
ORDER BY DatePart("m",[deptdate],1,1), DatePart("yyyy",[deptdate],1,1) DESC
, [DeptsSales Query].[DeptDesc]
PIVOT [DeptsSales Query].[DeptDesc];


DeptSales Query
SELECT [Depts].[DeptDesc], [DeptSalesDetail].[DeptNum],
[DeptSalesDetail].[DeptSales], [DeptSalesDetail].[DeptSalesQTY],
[DeptSalesDetail].[DeptRecID], [DeptDate].[DeptDate]
FROM Depts INNER JOIN (DeptDate INNER JOIN DeptSalesDetail ON
[DeptDate].[DeptDateRec] =[DeptSalesDetail].[DeptRecID]) ON
[Depts].[DeptNum] =[DeptSalesDetail].[DeptNum];

I would like the report to look something like this and need to know how I
should go about the building the query
Also DeptSales Query is above

Dept1
Month(Period) 2005Sales 2005Sales 2005Dept% 2004Dept% (col
Headings)
Jan $$ $$
##% ##%
Feb $$ $$
##% ##%

Dept2
Month(Period) 2005Sales 2005Sales 2005Dept% 2004Dept% (col
Headings)
Jan $$ $$
##% ##%
Feb $$ $$
##% ##%

The report can then calcuate the increase/decreases with it has the figures
from the query

Can someone put me in the right direction on this?

Thanks

Kevin