I did a search via google.com and found some previous posts that might be
helpful There are others, I'm sure.
--
Ken Snell
<MS ACCESS MVP>
----------------------------------------------------------------------------
------
----------------------------------------------------------------------------
------
----------------------------------------------------------------------------
------
From a Sep. 17, 2003 post by Michel Walsh (MS ACCESS MVP):
----------------------------------------------------------------------------
------
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)
----------------------------------------------------------------------------
------
----------------------------------------------------------------------------
------
A 1996 post that suggests the following approach for setting up a median
calculation (originally posted by "Gregor aka gregOr"):
----------------------------------------------------------------------------
------
What is the median? It's the central number in a series, (or the
average of the two middle numbers, if there are an even number of
elements in the table). So I take TOP 50 PERCENT of the table, sorted
by the field in question DESC, and then take the top element of the
result, sorted by the field. I make a UNION query that joins the result
of that query with a similar query that takes the top element, sorted
desc, of the TOP 50 PERCENT of the table, sorted by the field.
Finally, I take the average of the two results as the median. This
technique works whether there are an even or odd number of rows in the
table. It's got to be broken into at least two parts, since you can't
have a union query as a subquery in Access. Here's my SQL:
this is correct but not necessarily optimized.
Define query "Median" as:
SELECT Avg([fld1]) AS Median
FROM Query1;
where Query1 is as follows:
SELECT TOP 1 [fld1] FROM aa WHERE [fld1] in
(SELECT TOP 50 PERCENT [fld1] FROM AA ORDER BY [fld1])
ORDER BY [fld1] DESC
UNION
SELECT TOP 1 [fld1] FROM aa WHERE [fld1] in
(SELECT TOP 50 PERCENT [fld1] FROM AA ORDER BY [fld1] DESC)
ORDER BY [fld1];
----------------------------------------------------------------------------
------
----------------------------------------------------------------------------
------
Here's another suggestion from Steve Arbaugh (MS ACCESS MVP) on Feb. 13,
2001:
----------------------------------------------------------------------------
------
Median for example. If you have a recordset that contains a set of values
in one field in X number of rows, then set up a query to sort them
ascenting. Then in code, open that recordset, run to the end to find out
how many there are, and calc Fix(X/2) to round down to the nearest whole
number to get the position of the median value and lastly use the absolute
position property of the recordset (A97 and 2000 above only) to navigate to
that record to get the value of your median.
----------------------------------------------------------------------------
------
----------------------------------------------------------------------------
------
Here's a suggestion from Andy Lewis (dated Oct. 1, 2000):
----------------------------------------------------------------------------
------
There's no intrinsic SQL Median function, and as far as I can see, no simple
way to calculate the median inside a query. However, you can use the
following DMedian function which has similar syntax to the 'domain
aggregate' functions provided by Access:
'--------------------------------------------------
Function DMedian(Expr As String, Domain As String, Optional Criteria)
'--------------------------------------------------
'Returns the median value of expr in the domain.
'--------------------------------------------------
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim lngCount As Long
Dim Median
'On Error GoTo DMedian_Err
strSQL = "SELECT " & Expr & " FROM " & Domain
If Not IsMissing(Criteria) Then
strSQL = strSQL & " WHERE " & Criteria
End If
strSQL = strSQL & " ORDER BY " & Expr & " ASC;"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
With rst
If Not .EOF Then
.MoveLast
.MoveFirst
lngCount = .RecordCount
If lngCount Mod 2 = 1 Then 'record count is odd
.Move (lngCount + 1) / 2 - 1
Median = rst(0)
Else
.Move lngCount / 2 - 1
Median = rst(0)
.MoveNext
Median = (Median + rst(0)) / 2
End If
Else
Median = Null
End If
.Close
End With
Set rst = Nothing
DMedian = Median
Exit Function
DMedian_Err:
DMedian = Null
End Function
For example, to find the Median of the 'GroupID' field in the table
'MyTable', you would use
DMedian("GroupID", "MyTable")
