The best way to calculate median access 2007 when using a group

Question

I have a table containing a book, and then a few prices about the book (this is a very simplified selection):

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

I can easily calculate the average, but should there be a good 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 AMONG PRICES
BOOK1 12.333333333333333
BOOK2 5

Answer 1

There is no median in Jet SQL, unless it was added in 2007, but here's an idea on how to get it. 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 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 2

MS Access, VBA. - 50- . , ; SQL "Top 50 Percent" select. 50 ; 50%. . . "Top 50 Percent" , , .

Answer 3

, .

google -

Answer 4

-VBA , . Top 50 Perc Asc (Max) Top 50 Perc Desc (Min) Access , . Access , , +-0.00001, . Rank() VBA, Access , . , , VBA.