Solved

# Median Function in Access Query

Posted on 2010-08-29
1,010 Views
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
Question by:KP_SoCal
[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
• 2
• 2
• 2
• +2

LVL 60

Expert Comment

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 53

Expert Comment

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]
)
``````
0

LVL 53

Expert Comment

ID: 33554681
check here

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

LVL 45

Accepted Solution

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

LVL 60

Expert Comment

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

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

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

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

Question has a verified solution.

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

### Suggested Solutions

Oracle - BLOB Extract Line 2 19
Access Query function 4 51
Export individual report to pdf 2 40
how to use ROW_NUMBER() correctly 8 44
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
###### Suggested Courses
Course of the Month4 days, 22 hours left to enroll