Solved

# How calculate median

Posted on 2016-11-22
37 Views
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?
0
Question by:SteveL13
• 3
• 2

LVL 18

Accepted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
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
.MoveLast
.MoveFirst
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)
.MoveNext
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

Proc_Exit:
On Error Resume Next
'release object variables
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& "   GetMedian"

Resume Proc_Exit
Resume

End Function
``````
0

Author Comment

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

LVL 18

Expert Comment

``````=GetMedian("SELECT MyField FROM MyTable WHERE MyConditions")
``````

substitute :
MyField with the fieldname
MyTable with the table or query name
MyConditions with the criteria
0

Author Closing Comment

Thank you again.  Much appreciated.
0

LVL 18

Expert Comment

you're welcome ~ happy to help
0

## Featured Post

### Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…