Adding column totals to crosstab queries in ACCESS 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

grantschneider@gmail.com - 31 Jul 2007 10:35 GMT

I need to add column totals to the bottom of my crosstab. Here is the
code:

PARAMETERS [Month] Text ( 255 );
TRANSFORM Sum(MSF07.[Share Total]) AS [SumOfShare Total]
SELECT MSF07.Aircraft, MSF07_Crosstab1.[2007]
FROM MSF07 INNER JOIN MSF07_Crosstab1 ON MSF07.Aircraft =
MSF07_Crosstab1.Aircraft
WHERE (((Format([MSF07].[Date Closed],"mmm"))=[Month]))
GROUP BY MSF07.Aircraft, MSF07_Crosstab1.[2007]
PIVOT Format(MSF07.[Date Closed],"mmm");

Thanks

Grant

Reply to this Message

John Spencer - 31 Jul 2007 12:12 GMT

You can't do that in a query unless you use a UNION query.

And using a UNION query would require that you know in ADVANCE what columns
were being returned by the Crosstab query.

You might be able to do this in a report by adding the necessary controls
and calculations to a report footer.

Signature

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

>I need to add column totals to the bottom of my crosstab. Here is the
> code:
[quoted text clipped - 11 lines]
>
> Grant

Reply to this Message