BIGmiralli Frequent Poster
Joined: 17 Apr 2007 Posts: 38 Location: Boston, Massachusetts
|
Posted: Fri May 23, 2008 12:50 pm Post subject: 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.
Code: | 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 |
________
BMW 003 HISTORY |
|