Solved

Median Function in Access Query

Posted on 2010-08-29
8
1,002 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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
 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql help 8 55
Addition to SQL for dynamic fields 6 37
VB6 - Scroll Mouse wheel on Picturebox 13 42
Find results from sql within a time span 11 29
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 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