S
Sheryl Bradley
SQL has a Median function ... does Access?
Thanks!
Thanks!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
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.
"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.
G
Guest
Jeff, how can one go by doing that? Please explain.
Thanks. - Senka
Thanks. - Senka
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
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