Median Function in Access Query

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
KP_SoCalAsked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
This excellent EE article shows median and many more stats
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2529.html
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
HainKurtSr. System AnalystCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
HainKurtSr. System AnalystCommented:
check here

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

there is also another solution which is using a stored function...
0
 
Kevin CrossChief Technology OfficerCommented:
Thanks, aikimark, I am ashamed I forgot that article. That is exactly what you need and is written by a top Access Expert.
0
 
KP_SoCalAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
KP_SoCalAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.