Calculating the Modal class in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can calculate Mode (the formula is =Mode(number1,number2,etc.) in Excel,
but can't figure out how to do it in Access. I need to know the most common
value in a list of values. I don't think average will get me the same result.
Is this possible.
 
There is no Mode function in Access. It is possible to create one, however.

In the same way, there is no Median function in Access, but I created a
function similar to the built-in domain-aggregate functions (DMedian) that
will calculate it. (You can find the DMedian function here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=Median.mdb)

A DMode() function would work much the same way, but the internal
calculation would be different, of course. Unfortunately, I don't know much
about calculating a mode, so maybe you could answer this.

I understand that the mode is the most common value in a list. So
Mode(1,1,2,2,6,6,6,6,7,12), the mode would be 6. But what happens if the
result is not unique? ie Mode(1,1,2,4,4) How do you break the tie? Are there
any other rules for handling exceptions?


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
This has the same effect, where you want the mode of mycol

SELECT TOP 1 mytable.mycol
FROM mytable
GROUP BY mytable.mycol
ORDER BY Count(mytable.mycol) DESC;

You can build in other restrictions with a Where clause, and choose which of
multiples by sorting differently,

HTH

Peter.
 
Can this be extended to groupby another field mycol2, and show other fields
mycol3 and mycol4?
 
Back
Top