Thanks,
I tried playing around with it a little more, but I can't get it to work because the code tells it to open the record set of ALL data. Here is what I have so far.
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
In the select query that I am running I am grouping the data by "group number" and using this function as an expression "Expr1: MedianOfRst("Mkt Grp","Fee")".
I know that one way to work around this prolem would be to create seperate tables for each group number, however, in my actual data I have over 600 groups that I am trying to calculate that median fee on so that is not very reasonable. Also, it is important the I calulate the medain as this data can get very skewed by some really high fees, therefore, using the "average" function does not really tell me what I need!!
Thanks again for your help! Let me know if you think of any ideas to help.
Thanks,
Kjo1080