Help on Access Subquery

[cshannon]

I have a database that contains customer information for a company. Each customer is associated with a company sales rep. For example, customer a may be associated with rep #20, customer b may be associated with rep #30, and customer c my also be associated with rep #20. I am needing to write a query containing a subquery to determine which rep has the most customers.



So I assume my subquery will count the customers of each rep, and group them by rep num, as so:

SELECT COUNT(CustomerName) AS TotalCustomers
FROM Customer
GROUP BY RepNum

But then I don't know how to write a query over that to pull the RepNum of the rep that has the most customers. Please help!!

[June7]

Try:

SELECT TOP 1 RepNum, Count(CustomerName) AS TotalCustomers FROM Customers GROUP BY RepNum ORDER BY Count(CustomerName) DESC;