Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Calculating the Median of a Recordset

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access
View previous topic :: View next topic  
Author Message
BIGmiralli
Frequent Poster


Joined: 17 Apr 2007
Posts: 38
Location: Boston, Massachusetts

PostPosted: Fri May 23, 2008 12:50 pm    Post subject: Calculating the Median of a Recordset Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group