# MS ACCESS 2010 - query to calculate the MEDIAN of a field

I have a table with 2 fields:
AccountNumber
and
PaymentInterval

Each account will have several Payment intervals which were determined in a prior step by finding the difference in time between the payment dates.

I want to compute the MEDIAN of the payment intervals.
I can do a TOTALS query and get the Average.
How do I also get the MEDIAN for each AccountNumber SubTotal?

Ideally I'd like a subquery that I could place into a cell in the Totals query.

thanks!
Phil
LVL 1
###### Who is Participating?

Commented:
matthewspatrick has an excellent article here showing how to calculate Median and various other statistics in Access, with code and sample file included:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2529-Median-Mode-Skewness-and-Kurtosis-in-MS-Access.html
1

Commented:
you will need a UDF in VBA to do that

place this codes in a regular module

Function getMedian(sTable As String, sField As String) As Single
Dim rs As DAO.Recordset
Dim sSql As String
Dim j As Integer,varVal  As Single

sSql = "SELECT " & sField & " from " & sTable & " WHERE " & sField & ">0 Order by " & sField & ";"
Set rs = CurrentDb.OpenRecordset(sSql)
rs.MoveLast
j = rs.RecordCount
rs.Move -Int(j / 2)

If j Mod 2 = 1 Then 'odd number of elements
getMedian = rs(sField)
Else                'even number of elements
varVal = rs(sField)
rs.MoveNext
varVal = varVal + rs(sField)
getMedian = varVal / 2
End If
rs.Close
End Function

then create a query like this

SELECT  DISTINCT AccountNumber,PaymentInterval
, getMedian("Tablename","PaymentInterval") AS xMedian, [PaymentInterval]-[xmedian] AS PayDifference
FROM Tablename
1

Commented:
Thanks for recommending my article, Miriam :)
1

Commented:
Np - I've got that one bookmarked. :-)
0

Author Commented:
I chose Patrick's method because it supports a WHERE clause which could be useful.
And it gives a more thorough view of the issues.

I've seen some solutions that use ROWNUMBER to choose the median - They are purported to be faster. Does anyone have comments on that?

I split the points so that Patrick would get some - even though the reference is in Miriam's post.
0

Author Commented:
Just a bit more on this - I ran into an error 3360 because the query was too complex.
I resolved that by creating an intermediate table before running the query with the median code.
I hate to note that this is very slow - I had about 150k rows.
I'm hoping someone has a faster variation.
thanks
Phil
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.