Hi,
There is none predefined, you can use VBA to define your own.
==============================
Public Function Median(TableName As String, FieldName As String, Optional
Condition As String = vbNullString)
Dim str As String ' the SQL statement to fill the recordset
Dim rst As DAO.Recordset ' firehose recordset to get the data, in order
Dim n As Long ' number of records in the recordset
Dim x As Variant ' intermediate result
Dim db As Database : Set db=CurrentDb
str = " FROM " & TableName
If vbNullString = Condition Then
Else
str = str & " WHERE " & Condition
End If
Set rst = Db.OpenRecordset("SELECT " & FieldName & str & _
" ORDER BY " & FieldName, dbOpenForwardOnly,
dbReadOnly)
n = CurrentDb.OpenRecordset("SELECT COUNT(*)" & str).Fields(0).Value
rst.Move n \ 2 ' move at 1/2 (round down) the way
x = rst.Fields(0).Value
rst.Move n Mod 2 ' stay (move 0) or MoveNext (move 1)
Median = 0.5 * (x + rst.Fields(0).Value)
rst.Close
End Function
=========================
Use:
Median( "myTableName", "FieldToFindTheMedian")
Since the function takes into acount the presence of NULL, by default, you
may wish to remove them:
Median( "myTableName", "FieldToFindTheMedian", "NOT
FieldToFindTheMedian IS NULL" )
You can also incorporate a "group" condition:
Median( "myTableName", "FieldToFindTheMedian", "GroupingField= "
& GroupingField )
to take the median only from the records belonging to the suppplied grouping
value (data type is numerical, in this example)
Hoping it may help,
Vanderghast, Access MVP
