Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1031
  • Last Modified:

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
0
KP_SoCal
Asked:
KP_SoCal
  • 2
  • 2
  • 2
  • +2
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now