I use the following function to get the median of a particular field in a
table.
Lets assume that you want to run a query on "yourTable", group by the field
"SomeField" (where that field is textual) and compute the median on the field
[ValueField]. In this case, your query might look like:
Select SomeField, fnMedian("ValueField", "TableName", "[SomeField] = '" &
[SomeField] & "'") as Median
FROM TableName
GROUP BY SomeField
I have not tested this for use on MultipleCriteria for the grouping, but the
key would be building your Criteria string properly.
Public Function fnMedian(FieldName As String, Source As String, _
Optional ByVal Criteria As String = "") As Variant
Dim strSQL As String
Dim rs As DAO.Recordset
Dim lngRecCount As Long, lngHalfWay As Long
On Error GoTo MedianError
strSQL = "SELECT [" & FieldName & "] FROM [" & Source & "]"
If Criteria <> "" Then
strSQL = strSQL & " WHERE " & Criteria
End If
strSQL = strSQL & " ORDER BY [" & FieldName & "]"
'In case the user provides brackets around the field or
'table names, eliminate duplicates
strSQL = Replace(Replace(strSQL, "[[", "["), "]]", "]")
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If rs.EOF Then
fnMedian = Null
Else
rs.MoveLast
lngRecCount = rs.RecordCount
rs.MoveFirst
'the Move method appears to be relative, so you have to
'subtract 1 from that value
rs.Move ((lngRecCount \ 2) + (lngRecCount Mod 2)) - 1
fnMedian = rs(FieldName)
If (lngRecCount Mod 2) = 0 Then
rs.MoveNext
fnMedian = (fnMedian + rs(FieldName)) / 2
End If
End If
MedianExit:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Function
MedianError:
fnMedian = Null
MsgBox Err.number & Err.Description
Debug.Print "fnMedian error:", Err.number & Err.Description
Resume MedianExit
End Function
