Column Total

[Angel_G]

Is there a way to display column total in a query?
I have a query that gives me Prior year sales and current year sales but I
would like to include column totals for both columns. Is this possible?
Here is my query.

SELECT SDAL_USDK_SALES_UNION.ECCN, Sum(SDAL_USDK_SALES_UNION.PFYTotal) AS
[Prior Fiscal Year Total], Sum(SDAL_USDK_SALES_UNION.CFYTotal) AS [Current
Fiscal Year Total]
FROM SDAL_USDK_SALES_UNION
GROUP BY SDAL_USDK_SALES_UNION.ECCN;

Any suggestions are appreciated.

 

[Allen Browne]

Access 2007 can display a total at the bottom of the query's datasheet, but
previous versions cannot.

You can quite easily create a report, and sum the columns in the Report
Footer section.

 

[John Spencer]

Only way I know is to use a UNION query.



SELECT " " as Detail
, SDAL_USDK_SALES_UNION.ECCN
, Sum(SDAL_USDK_SALES_UNION.PFYTotal) AS [Prior Fiscal Year Total]
, Sum(SDAL_USDK_SALES_UNION.CFYTotal) AS [Current
Fiscal Year Total]
FROM SDAL_USDK_SALES_UNION
GROUP BY " ", SDAL_USDK_SALES_UNION.ECCN
UNION ALL
SELECT "Grand Total"
, Null, Sum(SDAL_USDK_SALES_UNION.PFYTotal) AS [Prior Fiscal Year Total]
, Sum(SDAL_USDK_SALES_UNION.CFYTotal) AS [Current
Fiscal Year Total]
FROM SDAL_USDK_SALES_UNION
GROUP BY SDAL_USDK_SALES_UNION.ECCN
ORDER BY Detail, Eccn

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

 

[Angel_G]

Thank you for your responses!
The information provided help me quite a bit.
Once again, thank you