Finding the common number, a DMax DCount problem? 1

[RichCraig]

Hi
I hope someone can quickly put me out of my misery. I have a table ORDERS that records CompanyID as a number field. I would like to find out the company (CompanyID) that has placed the most orders, but Im not too sure about how to combine the DMax(DCount()) aggregates.

Example
CompanyID
23
143 The SQL would return 23.
23
342

Thanks in advance!

 

[dhookom]

The following works in the sample Northwind.mdb
[tt]
SELECT TOP 1 Customers.CustomerID, Customers.CompanyName, Count(Orders.OrderID) AS CountOfOrderID
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName
ORDER BY Count(Orders.OrderID) DESC;
[/tt]
This could return more than one company if there is a tie.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[RichCraig]

Cheers dhookom, but Im not too hot on running SQLs yet, is there no way to use =Dlookups?

 

[dhookom]

Just create a query with the above SQL using your table and field names. Save the query as "qgrpMaxOrders". Then, you can use
=DLookup("CustomerID","qgrpMaxOrders")

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[RichCraig]

Thanks again dhookom, it was a friday and was seriously bugging me.