Highest occurrence of a value from a list

[tsison7]

I have a query which filters out orders of a certain item. This item is
ordered in varying quantities and I want to determine which quantity is
usually ordered (ie. highest occurence of that quantity). How would I do
this?

Thanks,

 

[Di Cook]

Check out dmax()

 

[tsison7]

Di,

dmax seems to only give you the maximum value of a given domain. I want to
find the qty in my table or query that shows up the most times. For instance,

ship date qty
1/1/08 5
1/2/08 10
1/3/08 5
1/4/08 5

I want to show that most orders are for a quantity of 5.

Thanks,

 

[James A. Fortune]

Di,

dmax seems to only give you the maximum value of a given domain. I want to
find the qty in my table or query that shows up the most times. For instance,

ship date qty
1/1/08 5
1/2/08 10
1/3/08 5
1/4/08 5

I want to show that most orders are for a quantity of 5.

Thanks,

Maybe:

http://groups.google.com/group/microsoft.public.access/msg/10c856261163ab24

James A. Fortune
(e-mail address removed)

 

[Ken Sheridan]

Try This:

SELECT Qty AS MostFrequentQty
FROM YourTable
GROUP BY Qty
HAVING COUNT(*) =
(SELECT MAX(QtyCount)
FROM
(SELECT COUNT(*) As QtyCount
FROM YourTable
GROUP BY Qty));

It should also find ties.

Ken Sheridan
Stafford, England