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

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

Solution

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