Solved

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

Posted on 2013-11-08
6
8,557 Views
Last Modified: 2013-11-14
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
0
Comment
Question by:philkryder
6 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 300 total points
ID: 39635081
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39635084
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
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 total points
ID: 39635103
Thanks for recommending my article, Miriam :)
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 61

Expert Comment

by:mbizup
ID: 39635108
Np - I've got that one bookmarked. :-)
0
 
LVL 1

Author Closing Comment

by:philkryder
ID: 39646112
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
 
LVL 1

Author Comment

by:philkryder
ID: 39650442
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now