Discussion Locked

mode and median in Access reports

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I can get mean and standard deviation of a column in an Access report by calling built-in functions. There seem to be no parallel functions for mode and median. How do I produce these statistics? I have an idea it may be doable via SQL, but the necessary grouping would lose me my output for the individual cases.

6 replies

by a. square

Median is found by:

Sort the elements.
Count the elements in a sample array and dividing by 2 (Same count above as below).
If the result is a whole number (x), then the median is equal to the xth element in the array when sorted.
If the result (x) is a decimal (16.5) then you find the average of element 16 and 17.

by a. square

>>"If the result (x) is a decimal (16.5) then you find the average of element 16 and 17."

Sorry what I meant to say was
If the result (x) is a decimal (16.5 for example) then you find the average of elements (x-0.5 and x+0.5, in this case the 16th and 17th elements in the array)

by john_wills

This I knew already: the problem is to get the calculation done in an Access report. My inclination is to transfer everything to Excel, insert mode and median there, then move the augmented array back to Access for the report, but something easier may be available - something more Access-ish.

This discussion is closed to new replies.