Find the number that appears the most times from a given list

[ChrisAccessDB01]

i have a list of numbers and for my analisis i need to find out which one appears the most often in my table i have tried to figure it out.

your help is appreciated
chris

 

[neileg]

I would do it with two nested queries. Create an aggregate query set to count the values. Then base a Top Values query set to TOP 1 on the aggregate query. Depending on where you need this value, DMax() may be more appropriate than the Top Values Query.

 

[mearle]

SELECT TOP 1 Number FROM List GROUP BY Number ORDER BY Count(Number) DESC

 

[ChrisAccessDB01]

how do i modify that for multiple entries i have this but it jhust brings up bacically every record in the list

SELECT TOP 1 NumberData1.[Number 1], NumberData1.[Number 2], NumberData1.[Number 3], NumberData1.[Number 4], NumberData1.[Number 5], NumberData1.[Number 6]
FROM NumberData1
GROUP BY NumberData1.[Number 1], NumberData1.[Number 2], NumberData1.[Number 3], NumberData1.[Number 4], NumberData1.[Number 5], NumberData1.[Number 6]
ORDER BY Count(NumberData1.[Number 1]) DESC , Count(NumberData1.[Number 2]) DESC , Count(NumberData1.[Number 3]) DESC , Count(NumberData1.[Number 4]) DESC , Count(NumberData1.[Number 5]) DESC , Count(NumberData1.[Number 6]) DESC;

 

[mearle]

Depends what you mean by "multiple entries".
The query is for records in a single field - it won't work with multiple fields.
You can list more records by changing or removing the TOP 1 part.

 

[ChrisAccessDB01]

well in my table i have 5 number records and i want the top in each but i have created a query out of the sub queries for each which gives me a list of the relevent ones but the list is rather long thanks tho