Calculate Median in Access Query

[kjo1080]

Does anyone know how to calculate the median in an Access Query? there is a formula for average but not for median. Does anyone know of a way to do this or of how to write a formula in a module that will work?

Any help is appreciated!

Thanks,

kjo1080

 

[giacomo]

maybe this will help: How to Use Code to Derive a Statistical Median

hth,
Giacomo

 

[kjo1080]

Thank you so much for your help. However, I cannot get this to work for what I am trying to do. I have a table the has group numbers in one column and fees in another column. I want to run a query that will give me the Median fee BY GROUP. The code you gave me works great but it is only giving me the median of ALL groups and it will not work for the seperate groups. The table is very large, but here is basically what is looks like:

Group Number Fee
222 $ 300
222 $ 400
222 $ 500
250 $ 500
250 $ 500
250 $ 500
250 $ 700
250 $ 1,000
250 $ 5,000


The Median for grp #222 should be $400 and the median for group 250 should be $600. The code you gave is showing the median to be $500 for both groups because that is the median on the entire data set. Any Ideas?

 

[giacomo]

maybe you could rework the function so that you can also pass in the group that will it will only provide the median for that group. That would probably be the fastest fix, it makes it a little less flexible though.

If you need more help post the code you have so far and I can help you modify it.

hth,
Giacomo

 

[kjo1080]

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.

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

 

[giacomo]

This is air code but here is what I was thinking. I added an optional group by list so that when you call the function you can add a list of fields to group by like this:

Expr1: MedianOfRst("Mkt Grp","Fee",[Group Number])

Public Function MedianOfRst(RstName As String, fldName As String, optional groupByList 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 
    
     '** NEW CODE ADDED BY GIACOMO **
     Dim strSQL as string
     
     strSQL = "SELECT [" & fldName & "] FROM [" & RstName & "]"

     if groupByList not null then
        strSQL = strSQL & " GROUP BY " & groupByList 
     end if

     Set RstOrig = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) 
     '** END OF NEW CODE **


     ' ** OLD CODE REPLACED IN THE BLOCK ABOVE **
     'Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset) 
     
     ' ** ORIGINAL CODE **
     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

hth,
Giacomo

 

[giacomo]

oops! wait a minute... that's not going to work. Try this instead:

Public Function MedianOfRst(RstName As String, fldName As String, optional strWhere 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 
    
     '** NEW CODE ADDED BY GIACOMO ** 
     Dim strSQL as string 
      
     strSQL = "SELECT [" & fldName & "] FROM [" & RstName & "]" 

     if strWhere not null then 
        strSQL = strSQL & " WHERE " & strWhere 
     end if 

     Set RstOrig = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) 
     '** END OF NEW CODE ** 


     ' ** OLD CODE REPLACED IN THE BLOCK ABOVE ** 
     'Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset) 
      
     ' ** ORIGINAL CODE ** 
     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

and then you'd call it like this: MedianOfRst("Mkt Grp","Fee","[Group Number] = " & [group number])

hth,
Giacomo

 

[kjo1080]

BRILLIANT!! . . . .THAT WORKED PERFECTLY!!

I have been trying to get that to work for a long time!! You saved me ALOT of work.

Thanks for your help!!

-kjo1080

 

[giacomo]

No Problem, glad it worked! ...I must admit I was a little worried when I screwed it up the first time around

Giacomo

 

[kjo1080]

One thing I should note for others that will find this code is that the statement

if strWhere not null then 
        strSQL = strSQL & " WHERE " & strWhere 
     end if

did not work. I had to take out the if statement and simply use

strSQL = strSQL & " WHERE " & strWhere

It worked for me when I did this, however, when doing it this way you MUST put a field in the strWhere field in the expression.

Thanks Again!! This is excellent!!