Counting Largest repeating value in a field

[Scott.McCoid]

I have a field in a table that uses a defined 2-place number format
(such as 28 or 53). Can anyone help with the coding to find which 2-
place number is used the most frequently, and how often it's used?
For instance, if the field has 28 in 1000 records, and 53 in 2500
records, the coding should return 53 and 2500. FYI - I intend to use
this in a report. Thanks in advance.

 

[Arvin Meyer [MVP]]

I have a field in a table that uses a defined 2-place number format
(such as 28 or 53). Can anyone help with the coding to find which 2-
place number is used the most frequently, and how often it's used?
For instance, if the field has 28 in 1000 records, and 53 in 2500
records, the coding should return 53 and 2500. FYI - I intend to use
this in a report. Thanks in advance.

Try something like:

SELECT [Number], Count([Number]) AS CountOfNumber
FROM tblMyData
GROUP BY [Number]
ORDER BY Count([Number]) DESC;