Determining the Median in an Access Field

  • Thread starter Thread starter B Hand
  • Start date Start date
B

B Hand

How do I calculate the median for a field in a query?
I'm trying to determine the median in a range of numbers
but I can't find any method - EXAMPLE: AVG determines the
average and SUM determines the sum of a range of numbers.
What do I use to determine the median?

Thanks
 
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
 
Back
Top