Solved

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

Posted on 2013-11-08
6
9,233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

749 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