asked on

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

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

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

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

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

philkryder

ASKER

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.

philkryder

ASKER

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