Calculating the mode value in a query?

daroy5 · #1 (**permalink**) 02-22-05, 12:49

In a summary query, I need to return the value that shows up the greatest number of times in a certain column of a table. Is there a function built in to Access or an easy way to perform this, or do I have to make my own function to handle this?

MikeTheBike · #2 (**permalink**) 02-23-05, 13:03

Hi

I think this query this will do what you want

Code:

SELECT TOP 1 Table1.Data
FROM Table1
GROUP BY Table1.Data
ORDER BY Count(Table1.Data) DESC;

I Think (have go to go), but this will return the item that occurres most in the 'Data' field in Table1 !

Hope that is wat you hat in find ?

MTB

daroy5 · #3 (**permalink**) 02-24-05, 13:29

This looks good - I will embed this in my query and see what it spits out. Thanks so much for your reponse; will keep you posted on the result!