Show Maximum Number in Report Footer

[Basenji]

In a report footer in Access 2003 I want to show the maximum number from the
group footers in the report. The last name group header has the name of the
client. In the detail section of the report there are a date of contact
[date] and type of contact [type] fields. The last name group footer has an
unbound control with the count of the contacts with the customer,
=Count([Date]), so I can quickly see the total number of contacts with each
customer. Now in the report footer in an unbound countrol I want to show the
largest number of contacts and also the name of the customer. The max
expression does not work, at least it did not for me. What do you suggest?
Thank you for your assistance.

 

[Duane Hookom]

You need to create a new query that returns this information. You can then
use either a subreport or add the new query to your reports record source.

For instance, if I wanted to display only the customer with the most orders
in the Orders table in Northwind, my SQL would be:

SELECT TOP 1 CustomerID, Count(OrderID) AS NumOfOrders
FROM Orders
GROUP BY CustomerID
ORDER BY Count(OrderID) DESC;