Crosstab: Sum of Columns over Row

[tommaccoy]

Dear All

I have created a crosstab with :

Grouped Rows Headings
Company
Product Name

Grouped Column Headings
Month of Sale

Sum Value

The query shows

Company Product Month a Month b Month c
ABS AA 12 34 56
ABS BA 16 35 46
CGG CA 5 8 86
GGS GC 18 9 5
GGS GD 19 4 96
MGS MA 17 8 16

My question is: how can i produce the sum of a Product over all the Months?


Company Product Month a Month b Month c Total
ABS AA 12 34 56 102
ABS BA 16 35 46 97
CGG CA 5 8 86 …
GGS GC 18 9 5 …
GGS GD 19 4 96 …
MGS MA 17 8 16 …

I managed over the report to generate the total of all Products for each
month

Company Product Month a Month b Month c
ABS AA 12 34 56
ABS BA 16 35 46
CGG CA 5 8 86
GGS GC 18 9 5
GGS GD 19 4 96
MGS MA 17 8 16
Total 87 98 ...

But I can not prduce the Product/Months sum

I tried to place, in the “Detail†area of the linked report, the sum function
and the addition function

=[08-1]+ [08-2]+ [08-3]+ [08-4]+ [08-5]+…
=Sum[08-1]+ Sum [08-2]+ Sum [08-3]+ Sum [08-4]+ Sum [08-5]+…

The problem is that it works only when every field has a value. If for
example a Product was not been sold in January, but only in Feb and Mar,...
the sum is not calculated.

If however a product has sold over every moth the sum is calculated!

Hope you guys can help me either over the query or with a function in the
report and thx in advance for your help

 

[John Spencer]

The easiest way might be to add another column to the crosstab that sums
across each line.

Field: ???
Total: Sum
Crosstab: Row Heading

Anther method would use the NZ function in your expressions
=NZ(Sum([08-1]),0) + NZ(Sum([08-2]),0)+ Nz(Sum([08-3]),0) + ...

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[steve dassin]

I first proposed an easy within crosstab solution to problems like this over
10 years ago. Only a few listened and understood You can find an outline
of this methodology in the following thread:
From: Steve Dassin
Subject: Re: getting a calculated field in a cross tab query?
Newsgroups: microsoft.public.access.queries
Date: 2002-01-26 17:58:00 PST
http://tinyurl.com/5m756a

Btw, does anyone have a stored copy of the original paper - "Heavy Duty
Crosstabs in Access 97"?

www.beyondsql.blogspot.com