Group Summary

[learning_codes]

Hi,

I want to know if there is a possible way of doing this. Your help
would be much appreciated.

I am able to do the count by each sport.

Baseball 23
Hockey 22
Tennis 54

but I"m looking if there is a way to add the last line:


Baseball 23
Hockey 22
Tennis 54
All Sports 99 << is that possible to add on the same query

Thanks

 

[Allen Browne]

You can show a total in the bottom row of your query in Access 2007 only.

While viewing the query results, click the Home tab of the ribbon, and
depress the Totals button in the Records group. A Total row appears at the
foot of the query, and you can choose Sum under the numeric column.

In earlier versions of Access, create a report to get the total.

 

[John Spencer]

Well there is a clunky alternative. That is to use a union query to get the
grand total and set the sorting by a third field

SELECT Sport, Count(Sport) as TheCount, "D" as TheOrder
FROM TheTable
GROUP BY Sport, "D"
UNION ALL
SELECT "TOTAL", Count(Sport) as TheCount, "T"
FROM TheTable
GROUP BY "Total", "T"
ORDER BY TheOrder

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