Calculate Median in Access Query

kjo1080

Active Member
Joined
Aug 2, 2004
Messages
325
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

kjo1080

Active Member
Joined
Aug 2, 2004
Messages
325
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

Active Member
Joined
Aug 2, 2004
Messages
325

ADVERTISEMENT

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
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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])

Code:
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796

ADVERTISEMENT

oops! wait a minute... that's not going to work. Try this instead:
Code:
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

Active Member
Joined
Aug 2, 2004
Messages
325
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
(y) No Problem, glad it worked! ...I must admit I was a little worried when I screwed it up the first time around :oops:

Giacomo
 

kjo1080

Active Member
Joined
Aug 2, 2004
Messages
325
One thing I should note for others that will find this code is that the statement

Code:
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
Code:
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!!
 

Forum statistics

Threads
1,136,369
Messages
5,675,360
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top