How do you find the most common value in a field

[Mike375]

I am OK with a number field as I can put it in Excel and use =Mode().

But that wont do text such as lastname, suburb etc. I can do suburb by doing postcode but no such equivalent from some others I want to do

 

[namliam]

In access you can use a group by query to count the field values then order by that count...

Select name, count(*)
from tblPeople
Group by name
Order by count(*) Desc

Will give you the most used names...

 

[Mike375]

Many thanks

 

[Mike375]

Apart from getting the most common field value that is a great way of seeing duplicates.

 

[namliam]

Among other things yes... The group by query is a very powerfull part of SQL