Median function?

J

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.
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top