adding one more column to corss tab

[vikram_singh]

Hi,

I have corss tab query which returns the sum of "retail_units" columns
based on the dates written in PIVOT FORMAT line in below written
query. these dates are basically the parameter which i am generating
at runtime by running vba code. all these columns are mandatory for
me to have value. But also i want to add one more column like sum of
all columns ......and this can be generated by writing in second line
of my query..like sum(Price_data.retail_units) as "Total Of All
Columns".. so this is one thing..kindly notice there are 13 month
which i am generating..... excluded "Total Of All Columns".... ...
NOW....i want to add one more column which will include SUM OF only
last 12 months NOT 13....That means it should exclude '2006
Apr'.....So is there any way by which i can add more more column to my
corss tab query.


TRANSFORM Sum(Price_Data.retail_units) AS Units
SELECT a.id, a.price_band
FROM Price_Data
INNER JOIN Price_PriceBandOrder AS a ON (Price_Data.sub_category =
a.sub_category) AND (Price_Data.price_band = a.price_band)
WHERE (((Price_Data.sub_category)='Colour Laser') AND
((Price_Data.dt)>=#4/1/2006# And (Price_Data.dt)<=#4/1/2007#))
GROUP BY a.id, a.price_band
ORDER BY a.id
PIVOT Format([dt],'yyyy mmm') In ('2006 Apr' ,'2006 May' ,'2006
Jun' ,'2006 Jul' ,'2006 Aug' ,'2006 Sep' ,'2006 Oct' ,'2006
Nov' ,'2006 Dec' ,'2007 Jan' ,'2007 Feb' ,'2007 Mar' ,'2007 Apr' );
)

Thanks in advance.

 

[John Spencer]

Try the following:

TRANSFORM Sum(Price_Data.retail_units) AS Units
SELECT a.id, a.price_band

, Sum(IIF(dt>-#5/1/2006#,Retail_units,Null)) as 12Month

FROM Price_Data
INNER JOIN Price_PriceBandOrder AS a
ON (Price_Data.sub_category =a.sub_category)
AND (Price_Data.price_band = a.price_band)
WHERE (((Price_Data.sub_category)='Colour Laser') AND
((Price_Data.dt)>=#4/1/2006# And (Price_Data.dt)<=#4/1/2007#))
GROUP BY a.id, a.price_band
ORDER BY a.id
PIVOT Format([dt],'yyyy mmm') In ('2006 Apr' ,'2006 May' ,'2006
Jun' ,'2006 Jul' ,'2006 Aug' ,'2006 Sep' ,'2006 Oct' ,'2006
Nov' ,'2006 Dec' ,'2007 Jan' ,'2007 Feb' ,'2007 Mar' ,'2007 Apr' );

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..