using compute by

[vikram_singh]

Hi,

M not that perfect in writing sql queries so thoght to write here if
someone could help me on this. I have one corsstab and the ouput is
someting like below:

Country Category PL
2007-1-12 2007-2-12 2007-3-12
------------ -------------- ------------
-------------- ---------------- --------------
US LaserJet AIO
12 45 45
US LaserJet BIZ
13 45 45
US LaserJet AIO
14 45 45
US LaserJet AIO
16 45 45

US Ink Jet BIZ
13 45 45
US Ink Jet AIO
14 45 45
US Ink Jet AIO
16 45 45

To have this i have written below queries and finally made one cross
tab by which i am generatting above values.

TRANSFORM Sum(report4_actuals.ANR) AS SumOfANR
SELECT report4_actuals.Country, report4_actuals.Category,
report4_actuals.PL
FROM report4_actuals
GROUP BY report4_actuals.Country, report4_actuals.Category,
report4_actuals.PL

PIVOT report4_actuals.dates;


So in the above query i wanted to implemet [Compute by category] so
that i can have sum of all laserjet and inkjet for each country.

Please suggest and would be great if somebody help me on this.

Regards,
Vikram

 

[Michel Walsh]

You will have to include the data twice, either by UNION ALL two crosstabs
(the easier way), either by using the data already properly UNIONed (or
JOINed) in the FROM clause (harder, and 'probably' not faster).


Hoping it may help,
Vanderghast, Access MVP