Answered by:
Finding Median in Access 2007
Question

My data has two columns: Size and Exposure_Prem. I'm trying to find the medium of "exposure premium" based on the size(either S or M or L). but my code doesn't return anything. Thank you!
My code is:
SELECT [Combined Data].Exposure_Prem,[Combined Data].Size
FROM [Combined Data]
GROUP BY [Combined Data].Exposure_Prem,[Combined Data].Size
HAVING Count([Combined Data].Size) Between (SELECT (Count([Combined Data].Size)/2)0.5
FROM [Combined Data]) And (SELECT (Count([Combined Data].Size)/2)+0.5 FROM [Combined Data])
ORDER BY [Combined Data].Exposure_Prem;Wednesday, August 24, 2016 9:30 PM
Answers

The following function is a somewhat crude way of finding the median by stepping though a recordset:
Public Function GetMedian(strTable As String, strColumn As String, Optional strFilter As String = "TRUE")
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dblVal As Double
Dim lngTotalRows As Long
Dim lngRowCounter As Long
strSQL = "SELECT " & strColumn & _
" FROM " & strTable & _
" WHERE " & strColumn & " IS NOT NULL" & _
" AND " & strFilter & _
" ORDER BY " & strColumn
Set rst = CurrentDb.OpenRecordset(strSQL)
With rst
.MoveLast
' get total number of rows with values
lngTotalRows = .RecordCount
.MoveFirst
Do While Not .EOF
lngRowCounter = lngRowCounter + 1
' if current position of row in sort order
' is an exact half of total number of rows in set,
' then median is arithmetic mean of values in
' this and next row
If lngTotalRows Mod 2 = 0 Then
If lngTotalRows / lngRowCounter = 2 Then
dblVal = .Fields(strColumn)
.MoveNext
GetMedian = (dblVal + .Fields(strColumn)) / 2
Exit Do
End If
Else
' if current position of row in sort order
' is an exact half of total number of rows in set + 1,
' then median is value in this row
If (lngTotalRows + 1) / lngRowCounter = 2 Then
GetMedian = .Fields(strColumn)
Exit Do
End If
End If
.MoveNext
Loop
End With
End Function
In your case you'd call it as follows:
SELECT DISTINCT Size,
GetMedian("[Combined Data]","Exposure_Prem","Size = """ & Size & """") AS Median_Exposure_Prem
FROM [Combined Data];
Note that the Median is not necessarily a value in the Exposure_Prem column. If a subset of rows per size is an even number of rows it is the arithmetical mean of the middle two rows in the sort order.Ken Sheridan, Stafford, England
 Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Friday, September 2, 2016 8:33 AM
 Marked as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Friday, September 2, 2016 8:33 AM
Wednesday, August 24, 2016 11:15 PM 
Hi grace,
visit the link below will give an example of that.
ACC: How to Use Code to Derive a Statistical Median
Finding the Median Value for Fields in Access 2007
Regards
Deepak
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey. Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Friday, September 2, 2016 8:33 AM
 Marked as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Friday, September 2, 2016 8:33 AM
Thursday, August 25, 2016 12:39 AM
All replies

Hi. It might be easier to create a custom function to find the Median and then use it in your query. Just a thought...Wednesday, August 24, 2016 9:43 PM

The following function is a somewhat crude way of finding the median by stepping though a recordset:
Public Function GetMedian(strTable As String, strColumn As String, Optional strFilter As String = "TRUE")
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dblVal As Double
Dim lngTotalRows As Long
Dim lngRowCounter As Long
strSQL = "SELECT " & strColumn & _
" FROM " & strTable & _
" WHERE " & strColumn & " IS NOT NULL" & _
" AND " & strFilter & _
" ORDER BY " & strColumn
Set rst = CurrentDb.OpenRecordset(strSQL)
With rst
.MoveLast
' get total number of rows with values
lngTotalRows = .RecordCount
.MoveFirst
Do While Not .EOF
lngRowCounter = lngRowCounter + 1
' if current position of row in sort order
' is an exact half of total number of rows in set,
' then median is arithmetic mean of values in
' this and next row
If lngTotalRows Mod 2 = 0 Then
If lngTotalRows / lngRowCounter = 2 Then
dblVal = .Fields(strColumn)
.MoveNext
GetMedian = (dblVal + .Fields(strColumn)) / 2
Exit Do
End If
Else
' if current position of row in sort order
' is an exact half of total number of rows in set + 1,
' then median is value in this row
If (lngTotalRows + 1) / lngRowCounter = 2 Then
GetMedian = .Fields(strColumn)
Exit Do
End If
End If
.MoveNext
Loop
End With
End Function
In your case you'd call it as follows:
SELECT DISTINCT Size,
GetMedian("[Combined Data]","Exposure_Prem","Size = """ & Size & """") AS Median_Exposure_Prem
FROM [Combined Data];
Note that the Median is not necessarily a value in the Exposure_Prem column. If a subset of rows per size is an even number of rows it is the arithmetical mean of the middle two rows in the sort order.Ken Sheridan, Stafford, England
 Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Friday, September 2, 2016 8:33 AM
 Marked as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Friday, September 2, 2016 8:33 AM
Wednesday, August 24, 2016 11:15 PM 
What if I just want to calculate the median of exposure premium without differentiating between different sizes?Wednesday, August 24, 2016 11:22 PM

Hi grace,
visit the link below will give an example of that.
ACC: How to Use Code to Derive a Statistical Median
Finding the Median Value for Fields in Access 2007
Regards
Deepak
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey. Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Friday, September 2, 2016 8:33 AM
 Marked as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Friday, September 2, 2016 8:33 AM
Thursday, August 25, 2016 12:39 AM 
What if I just want to calculate the median of exposure premium without differentiating between different sizes?
The third argument of my function is optional, so:
SELECT DISTINCT
GetMedian("[Combined Data]","Exposure_Prem") AS Median
FROM [Combined Data];
Or you can call the function in exactly the same way anywhere, e.g. as the ControlSource property of a text box control in a form or report.Ken Sheridan, Stafford, England
Thursday, August 25, 2016 10:55 AM