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
philkryderAsked:
Who is Participating?
 
mbizupCommented:
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
 
Rey Obrero (Capricorn1)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
 
Patrick MatthewsCommented:
Thanks for recommending my article, Miriam :)
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mbizupCommented:
Np - I've got that one bookmarked. :-)
0
 
philkryderAuthor 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
 
philkryderAuthor 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.

All Courses

From novice to tech pro — start learning today.