# Calculate Median in Access Query

#### kjo1080

##### Active Member
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
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
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

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

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
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
No Problem, glad it worked! ...I must admit I was a little worried when I screwed it up the first time around

Giacomo

#### kjo1080

##### Active Member
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!!

Replies
8
Views
152
Replies
3
Views
190
Replies
12
Views
150
Replies
2
Views
40
Replies
9
Views
64

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?

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