Median function?

[Sheryl Bradley]

SQL has a Median function ... does Access?

Thanks!

 

[Jeff Boyce]

Sheryl

"Median" is not one of the aggregate functions I'm aware of in Access ...
but you could establish a reference to the Excel object library and "borrow"
one from there.

 

[Guest]

Jeff, how can one go by doing that? Please explain.
Thanks. - Senka

 

[Michel Walsh]

Hi,


Another possibility is to open a recordset, and to average the values at
n\2 :

------------------------------------------
Public Function DMedian(FieldName As String, TableName As String) As Double
Dim rst As ADODB.Recordset
Dim n As Long
Dim x As Double

Set rst = CurrentProject.Connection.Execute("SELECT [" & FieldName & _
"] FROM [" & TableName & "] WHERE NOT [" & _
FieldName & "] IS NULL ORDER BY [" & FieldName & "]")

n = CurrentProject.Connection.Execute("SELECT COUNT([" & _
FieldName & "]) FROM [" & TableName & "]").Fields(0).Value

If 0=n Mod 2 then

rst.Move n \ 2
x = rst.Fields(0).Value
rst.Move 1
DMedian = (x + rst.Fields(0).Value) / 2

Else

rst.Move 1+n \ 2
DMedian = rst.Fields(0).Value

End If

rst.Close

End Function
--------------------------------------------



ex.:


median = DMedian( "field1", "myTable")



Hoping it may help,
Vanderghast, Access MVP