Crosstab Query - Need Grand Totals

[Bob]

I have a crosstab query and I would like to know how to add a grand total at
the bottom for the three columns I have.
Here is my SQL code:

SELECT MotionWithBearings_YTD.[MI Loc], MotionWithBearings_YTD.[Branch ID],
MotionWithBearings_YTD.Name, MotionWithBearings_YTD.[Product Code],
MotionWithBearings_YTD.[YTD-2006], MotionWithBearings_YTD.[YTD-2007],
MotionWithBearings_YTD![YTD-2007]-MotionWithBearings_YTD![YTD-2006] AS
Variance
FROM MotionWithBearings_YTD
ORDER BY MotionWithBearings_YTD![YTD-2007]-MotionWithBearings_YTD![YTD-2006];

Thanks.

 

[John Spencer]

You might T R Y a union query.


SELECT "Details" as LineType
, MotionWithBearings_YTD.[MI Loc]
, MotionWithBearings_YTD.[Branch ID]
, MotionWithBearings_YTD.Name
, MotionWithBearings_YTD.[Product Code]
, MotionWithBearings_YTD.[YTD-2006]
, MotionWithBearings_YTD.[YTD-2007]
, MotionWithBearings_YTD![YTD-2007]-MotionWithBearings_YTD![YTD-2006] AS
Variance
FROM MotionWithBearings_YTD


UNION ALL

SELECT "Totals"
, MotionWithBearings_YTD.[MI Loc]
, MotionWithBearings_YTD.[Branch ID]
, MotionWithBearings_YTD.Name
, MotionWithBearings_YTD.[Product Code]
, Sum(MotionWithBearings_YTD.[YTD-2006])
, Sum(MotionWithBearings_YTD.[YTD-2007])
, Sum(MotionWithBearings_YTD![YTD-2007]-
MotionWithBearings_YTD![YTD-2006])
FROM MotionWithBearings_YTD
GROUP BY MotionWithBearings_YTD.[MI Loc]
, MotionWithBearings_YTD.[Branch ID]
, MotionWithBearings_YTD.Name
, MotionWithBearings_YTD.[Product Code]
ORDER BY 1, 8

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