from Amazon
Thursday, May 20, 2010
How to calculate the Median value in MS Access
SELECT
(t2.medianT + b2.medianB)/2 as medianValue
, t2.medianT
, b2.medianB
FROM
(
SELECT MIN(top50) AS medianT
FROM (
SELECT TOP 50 PERCENT HR as top50
FROM Table1
WHERE HR IS NOT NULL
ORDER BY HR DESC
) t
) t2
,
(
SELECT MAX(top50) AS medianB
FROM (
SELECT TOP 50 PERCENT HR as top50
FROM Table1
WHERE HR IS NOT NULL
ORDER BY HR ASC
) b
) b2
;