Sum row in query/report

[Guest]

I have created the following crosstab query

PARAMETERS [Enter Customer] Text ( 255 );
TRANSFORM Sum([Main Table].Quanity) AS SumOfQuanity
SELECT [Main Table].Customer, [Main Table].Spec, [Main Table].Lot, [Main
Table].sm, Sum([Main Table].Quanity) AS [Total Of Quanity]
FROM [Main Table]
WHERE ((([Main Table].Customer)=[Enter Customer]))
GROUP BY [Main Table].Customer, [Main Table].Spec, [Main Table].Lot, [Main
Table].sm
PIVOT Format([Date],"ww") In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53);

I have also generated a report based on the query. I need to list the
[quanity] of each [spec] by week. Obviously though 53 weeks is too much for
one page. So I have set up the report to show 1 Quarter per page. I would
like to sum the [quanity] of each [spec] over the quarter (13 weeks) and add
that into the report. However im not sure how to perform the sum. I imagine
i need to due this in the query building an expression but have had no luck
to date. Any suggestions?

 

[Michel Walsh]

Hi,


Use

PIVOT Format([Date], "q") IN( "1", "2", "3", "4")

rather than the actual PIVOT clause.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Your right that would create a table based on quarters not weeks. However I
need the query based on weeks for the report. So your solution wont work for
my problem. What I think I need to do is somehow add 4 more columns using
aggregate functions to add add the weeks for each quarter. However my
attempts to do so have not been sucessful.

How can i make a column in my query that would do the following

=[1]+[2]+...[13]