Show Maximum Number in Report Footer


B

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.
 
Ad

Advertisements

D

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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top