Summing by year in a cross tab query

[Guest]

Hi,

I created a cross tab query wizard that has the following fields:
CustomerName, Type, and Year as row names in that order and then month names
as column headers. So my data from my cross tab query look like the
following:

CustomerName Type Year January February March April............
ABC Corp A 2005 $100 $150 $200 $250
ABC Corp A 2006 $200 $250 $300 $350
ABC Corp A 2007 $300 $350 $375
XYZ Corp A 2005 $200 $250 $300 $350
XYZ Corp A 2006 $325 $375 $400 $425
XYZ Corp A 2007 $100 $200 $300

I am trying to build a report using the wizard that will sum not only by
Sales Type (There are about four sales types A,B,C and D) but by Year. So
in this example, if ABC Corp and XYZ Corp were the only companies that were
Type A clients, I would want to know that the 2005, 2006 and 2007 sales were
by month for each sales type. Can someone help me with the summing on my
report?

Thanks,

 

[Allen Browne]

Try adding another field to the query:
Field Amount (i.e. the same field your Value is based on)
Crosstab Row Heading
Total Sum

 

[Duane Hookom]

I think you are attempting to mix you sorting and grouping. This generally
requires subreports. You need to create a query that groups by desired
fields and then use the query as the record source for a subreport.