Access Report to Include Mode Calcs

March 20, 2009 at 12:30:48
Specs: Windows XP
I have a series of reports that I have created in MS Access. These reports need to include Mean and Mode at the end of the report. I know how to calculate the Mean (AVG) but I need help calculating the Mode (most frequent occurance).

Any guidance would be greatly appreciated.


See More: Access Report to Include Mode Calcs

Report •


#1
March 25, 2009 at 08:47:05

I set up a simple table "testValues" with field "valu". Added some fictitious data, then created a query.

Query Groups the data based on value of valu and Counts the number of occurances of that value.
Wrap another query around that to find which valu occurs most often


grp is the mode (modal value)

Code is:

Select grp from
(SELECT testValues.valu AS Grp, Count(testValues.valu) AS Cnt
FROM testValues
GROUP BY testValues.valu
ORDER BY Count(testValues.valu) DESC
)
where Cnt = (select max(cnt) from
(
SELECT testValues.valu AS Grp, Count(testValues.valu) AS Cnt
FROM testValues
GROUP BY testValues.valu
)
)


Report •
Related Solutions


Ask Question