locked
Finding Median in Access 2007 RRS feed

  • 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

    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.

    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

    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.

    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