• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

How calculate median

I have a report that shows the record ID and a number field in the detail section.  I can get the average with the sum of the number field divided by the record count in the report footer.

But how can I calculate the median average?
  • 3
  • 2
1 Solution
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
This code averages the 2 middle values if there is not an odd number of values -- you may want to change this. Send it the SQL statement you want it to use where the first field is what you want the median of.
Function GetMedian(sSQL As String) As Double
' 121103 Crystal
' strive4peace

   On Error GoTo Proc_Err

   Dim db As DAO.Database _
      , rs As DAO.Recordset
   Dim nDbl As Double _
      , nNumValues As Long
   Set db = CurrentDb
   Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
   If Not rs.EOF Then
      With rs
         nNumValues = .RecordCount
         Select Case True
         Case nNumValues = 1
            GetMedian = Nz(.Fields(0).Value, 0)
         Case (nNumValues / 2 - nNumValues \ 2) < 0.001 'even
            'average 2 middle values
            .Move ((nNumValues \ 2) - 1)
            nDbl = Nz(.Fields(0).Value, 0)
            GetMedian = (nDbl + Nz(.Fields(0).Value, 0)) / 2
         Case Else 'odd
            .Move (nNumValues \ 2)
            GetMedian = Nz(.Fields(0).Value, 0)
         End Select
      End With 'rs
   End If
   On Error Resume Next
   'release object variables
   If Not rs Is Nothing Then
      Set rs = Nothing
   End If
   Set db = Nothing
   Exit Function
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetMedian"

   Resume Proc_Exit
End Function

Open in new window

SteveL13Author Commented:
Must be doing something wrong or not complete.  I copy/pasted your code in the bottom of the reports VBA editor.  Now how do I make the textbox populate with the median on the report?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
=GetMedian("SELECT MyField FROM MyTable WHERE MyConditions")

Open in new window

substitute :
MyField with the fieldname
MyTable with the table or query name
MyConditions with the criteria
SteveL13Author Commented:
Thank you again.  Much appreciated.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now