Figuring Median

[Brian Wagoner]

How do you figure median in access 2003. I am trying to
report on average and median times for things like
emergency room to CT, CT done to CT report. Help my
bosses eyes are turning red. Thanks Brian

 

[Tim Ferguson]

How do you figure median in access 2003.

Open a snapshot recordset, sorted on the field you are interested in.

Get the .RecordCount value:
if it's an even number
get the n/2 and n/2 +1 'th records and use the mean value

else if it's an odd number
get the (n+1)/2 'th value and use that

else
there are no records in the recordset

end if

There is example code around on the web -- I think Steve Lebans has some on
<http://www.lebans.com>

Hope that helps


Tim F

 

[Sam]

Here's a function I use...

It works just like the included domain functions...

Public Function DMedian(Expr As String, Domain As String, Optional Criteria
As String) As Double
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Temp As Double
Dim OrderedSQL As String

'construct SQL
OrderedSQL = "SELECT " & Expr
OrderedSQL = OrderedSQL & " FROM " & Domain
If Criteria <> "" Then
OrderedSQL = OrderedSQL & " WHERE " & Criteria
End If
OrderedSQL = OrderedSQL & " ORDER BY " & Expr & ";"

Set db = CurrentDb
Set rs = db.OpenRecordset(OrderedSQL)

rs.MoveLast
NumRecords = rs.RecordCount
rs.MoveFirst
rs.Move Int(NumRecords / 2)
Temp = rs.Fields(Expr)

If NumRecords / 2 = Int(NumRecords / 2) Then

'there is an even number of records
rs.MovePrevious
Temp = Temp + rs.Fields(Expr)
DMedian = Temp / 2

Else

'there is an odd number of records
DMedian = Temp

End If

rs.Close
db.Close


End Function

HTH
Sam