Best Way To Calculate A Median In Access 2007 When Using Group By

Question

I have a table which contains a book and then multiple prices about the book (this is a highly simplified sample):

ID  BOOK PRICE
1   BOOK1 10
2   BOOK1 15
3   BOOK1 12
4   BOOK2 8
5   BOOK2 2

I am easily calculating the average, but there must be a nice way to calculate the median?

Current SQL:

SELECT DISTINCTROW Books.BOOK, Avg(Books.PRICE) AS [Avg Of PRICE]
FROM Books
GROUP BY Books.BOOK;

Results:

BOOK    Avg Of PRICE
BOOK1   12.3333333333333
BOOK2   5

Answer 1

There is no built in function - so you have to use code to loop through the records and calc the median yourself.

Use google - there are lots of code samples out there

Answer 2

There is no Median in Jet SQL, unless it has been added for 2007, but here is an idea of how to get one. You will need ...

Some SQL ...

SELECT Statistics.Month, Sum(([SentTo])) AS [Sum Sent], fMedian("Statistics","Month",[Month],"SentTo") AS [Median Sent]
FROM Statistics
GROUP BY Statistics.Month;

And a User Defined Function (UDF).

Function fMedian(SQLOrTable, GroupFieldName, GroupFieldValue, MedianFieldName)
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset(SQLOrTable, dbOpenDynaset)

If IsDate(GroupFieldValue) Then
    GroupFieldValue = "#" & GroupFieldValue & "#"
ElseIf Not IsNumeric(GroupFieldValue) Then
    GroupFieldValue = "'" & Replace(GroupFieldValue, "'", "''") & "'"
End If

rs1.Filter = GroupFieldName & "=" & GroupFieldValue
rs1.Sort = MedianFieldName

Set rs = rs1.OpenRecordset()
rs.Move (rs.RecordCount / 2)

If rs.RecordCount Mod 2 = 0 Then
    varMedian1 = rs.Fields(MedianFieldName)
    rs.MoveNext
    fMedian = (varMedian1 + rs.Fields(MedianFieldName)) / 2
Else
    fMedian = rs.Fields(MedianFieldName)
End If

End Function

From: LessThanDot Wiki

Answer 3

Median can be be calculated in MS Access using just regular queries and without VBA. Median is the 50th percentile. So, create select queries normally; then go into SQL view and include "Top 50 Percent" after select keyword. Sort ascending for bottom 50 percent; sort descending for top 50 percent. Then find maximum of bottom percent result set and minimum of top percent result set. The average of these two is the median. When using "Top 50 Percent", make sure criteria in the query is specific for that result set from which median will be calculated.