 Microsoft Access Office VB VBA Help and Examples @import url(http://www.google.com/cse/api/branding.css);   HOME MS ACCESS MS OUTLOOK MS EXCEL VB and VBA FREE PROGRAMS PERSONAL
 ACCESS GENERAL QUERIES FORMS REPORTS MODULES RANTS TUTORIALS

# 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           RstSorted.MoveNext           MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value           MedianTemp = MedianTemp / 2      Else           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