Find the most popular answer in a field

[PMV1964]

Hi I'm using Access 2013 with a basic knowledge of Access.
I have a report that that will bring up a list of data. In that data is a field that has a production run size. In the footer I have worked out the max run size / min run size / average run size. What I need to know is can I get the most occurring run size? So to find the run size most used.
Thanks in advance

[ranman256]

in a query do a COUNT of the field, (or SUM if numeric)

sort descending

[PMV1964]

in a query do a COUNT of the field, (or SUM if numeric)

sort descending

I don't think this is quite right,
for example in the report there is 10 records with 2,000 in this field and 12 records with 7,000, 7 with 7,500 and 8 with 9,000.
Min = 2,000 - which I can do
Max = 9,000 - which I can do

Most = 7,000 which I can't do

[orange]

See this query which will give the value with the highest count.(most replicated value)

SELECT Table1.myField, Count(Table1.myField) AS CountOfmyField
FROM Table1
GROUP BY Table1.myField
ORDER BY Count(Table1.myField) DESC;

This is based on a Table TABLE1
with fields
ID autonumber PK
MyField Number

MyField valued as per your sample.

Result:

myField	CountOfmyField
7000	12
2000	10
9000	9
7500	7

You can do it with one query if you modify the previous example to

select top 1 * from 
(SELECT Table1.myField, Count(Table1.myField) AS CountOfmyField
FROM Table1
GROUP BY Table1.myField
ORDER BY Count(Table1.myField) DESC);

to give this result

myField
CountOfmyField

7000
12

[PMV1964]

See this query which will give the value with the highest count.(most replicated value)

SELECT Table1.myField, Count(Table1.myField) AS CountOfmyField
FROM Table1
GROUP BY Table1.myField
ORDER BY Count(Table1.myField) DESC;

This is based on a Table TABLE1
with fields
ID autonumber PK
MyField Number

MyField valued as per your sample.

Result:

myField	CountOfmyField
7000	12
2000	10
9000	9
7500	7

You can do it with one query if you modify the previous example to

select top 1 * from 
(SELECT Table1.myField, Count(Table1.myField) AS CountOfmyField
FROM Table1
GROUP BY Table1.myField
ORDER BY Count(Table1.myField) DESC);

to give this result

myField
CountOfmyField

7000
12

Thanks for your help - took a little time to get my head around it, but now I totally understand the process and it gives me the answers I require