First 10 numbers

[Deenos]

I have a query that pulls together states from a program. I group the states
and added the Count aggregate to the state field. I want access to display
the top 10 states including an extra entry if two states have the same number.

I hope this is clear.

thank you,

 

[John Spencer]

Using top 10 should work

SELECT TOP 10 States, Count(SomeField)
FROM SomeTable
GROUP BY States
ORDER BY Count(SomeField) Desc

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

Open the query in SQL View. Change the first line to say:

SELECT TOP 10 state

Make sure to sort (AKA group by) the Count([State]) field in DESC order.

However the above will only return extra records if there is a tie for the
10th position. A tie for only the 5th position will only return 10 records.
If there are more than 2 records tied for 10th, all ties will be returned.

 

[Deenos]

Great. Thank you. It worked
--
Deenos

Using top 10 should work

SELECT TOP 10 States, Count(SomeField)
FROM SomeTable
GROUP BY States
ORDER BY Count(SomeField) Desc

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

.