Microsoft Access Office VB VBA Help and Examples

Calculating the Median of a Recordset

The median of a set of numbers is the value where half the number are less than the median and half the numbers are more than the median. If the count of numbers is odd the mid point is used if the count of numbers is even the average of the two values around the midpoint is used. Example

Median of 1,2,3,4,5  is 3
Median of 1,2,3,4,5,6 is 3.5
Median of 1,2,3,3,3,6 is 3

The function below shows you how to calculate the median in access. Paste the following into a new or existing module and call it from anywhere to get the mean of a field in any recordset.

Please note there is no error handling so make sure the field is a valid number and the recordset exists and has one or more records.

The test sub shows you how to call it. This was built in the Northwind database so you can use it there for test purposes.

Public Function MedianOfRst(RstName As String, fldName As String) As Double
     'This function will calculate the median of a recordset. The field must be a number value.
     Dim MedianTemp As Double
     Dim RstOrig As Recordset
     Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
     RstOrig.Sort = fldName
     Dim RstSorted As Recordset
     Set RstSorted = RstOrig.OpenRecordset()
     If RstSorted.RecordCount Mod 2 = 0 Then
          RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
          MedianTemp = RstSorted.Fields(fldName).Value
          MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value
          MedianTemp = MedianTemp / 2
          RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
          MedianTemp = RstSorted.Fields(fldName).Value
     End If
     MedianOfRst = MedianTemp
End Function

Private Sub test()
     MsgBox MedianOfRst("Orders", "Freight")
End Sub
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.