First 10 numbers

  • Thread starter Thread starter Deenos
  • Start date Start date
D

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,
 
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
 
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.
 
Great. Thank you. It worked
--
Deenos


John Spencer said:
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

.
Click to expand...
 
Back
Top