Calculating the Modal class in Access

[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.

 

[Roger Carlson]

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

 

[Guest]

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.

 

[Stewart Jefferys]

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