Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-08
6
Medium Priority
?
10,668 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 1200 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
1
 
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
1
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 800 total points
ID: 39635103
Thanks for recommending my article, Miriam :)
1
How to Create Failover DNS Record Sets in Route 53

Route 53 has the ability to easily configure DNS record sets specifically for failover scenarios. These failover record sets can be configured to failover to full-blown deployments in other regions or to a static HTML page that informs your customers of the issue.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

721 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