CODEWITHSUNDEEP
sqlms-accessms-access-2007median
NoahD
NoahD

Reputation: 8302

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

Upvotes: 4

Views: 22483

Answers (4)

Red Sole Survivor
Red Sole Survivor

Reputation: 1

I've tried to use non-VBA methods and they are all somewhat limited and not being able to give you the most accurate result for a bigger data set. For the Top 50 Percent Asc (Max) and Top 50 Percent Desc (Min) method, Access has a limitation and it omits single values when ordered in ascending order. For rounding, Access is using bankers rounding so it's not always rounding up depending on the value itself, you need to add a trailing value of +-0.00001 if you want to round up or down. I also tried another Rank()method without VBA and Access is having trouble ranking values that has at least one pair of identical values. For reasons above, I strongly suggest sticking to creating customize function in VBA.

Upvotes: 0

Alan
Alan

Reputation: 19

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.

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91356

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

Upvotes: 5

DJ.
DJ.

Reputation: 16247

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

Upvotes: -1

Related Questions