Solved

Median Function in Access Query

Posted on 2010-08-29
8
996 Views
Last Modified: 2012-05-10
Excel has a wonderful function known as the “Median Function” that unfortunately is not available in Access.  I know there must be a way to get the median value for a group of records in my database, but I’m not sure how to implement.  

In my attached database, ‘tblRawData’ contains my raw data, and ‘tblMedianResults’ reflects what my expected results would be after using the “median” function (if it were available).  If you look at ‘tblAvgResults’ compared to my raw data, you’ll see why I do not want to utilize the “Average” function.

Here’s a thread pertaining to this topic, but I wasn’t able to make heads or tails of it.  Any direction you experts could provide would be greatly appreciated.  Thanks!

Median.mdb
0
Comment
Question by:KP_SoCal
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33554532
Have you tried simply creating a function that loads the values into an array and then picking the index in the middle of the array and return its value as the Median?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33554677
maybe this...
SELECT Avg([fld1]) AS Median

FROM (

SELECT TOP 1 [fld1] FROM aa WHERE [fld1] in (SELECT TOP 50 PERCENT [fld1] FROM AA ORDER BY [fld1]) ORDER BY [fld1] DESC

UNION

SELECT TOP 1 [fld1] FROM aa WHERE [fld1] in (SELECT TOP 50 PERCENT [fld1] FROM AA ORDER BY [fld1] DESC)

ORDER BY [fld1]

)

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33554681
check here

http://www.pcreview.co.uk/forums/thread-1628540.php

there is also another solution which is using a stored function...
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 33555095
This excellent EE article shows median and many more stats
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2529.html
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33555102
Thanks, aikimark, I am ashamed I forgot that article. That is exactly what you need and is written by a top Access Expert.
0
 

Author Closing Comment

by:KP_SoCal
ID: 33555166
This is just what I needed!  Thanks so much.  I think I can figure it out from here.  If not, I'll create a separate post.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33555281
KP_SoCal,

So glad that that article hit the spot for you (and thanks to aikimark for recommending it!).

If you haven't already done so, I would really appreciate it if you would go to the article and click 'yes' for 'was this helpful?'.

Patrick
0
 

Author Comment

by:KP_SoCal
ID: 33555746
Done!  Fantastic article on this Patrick.  Really appreciate you taking the time to put all that together.  Information like this is why I continue to renew my subscription with ExpertsExchange month after month. ;-)

KP
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 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

11 Experts available now in Live!

Get 1:1 Help Now