Median

R

Ronny84

I want to add a column to a query to calculate the medians of one field (#
Days Open) in a table.

Any help would be greatfully received.

Thanks
 
J

Jeff Boyce

Consider adding a Reference to Excel in a module. I don't believe Access
has a native "Median" function.

You will probably have to write a procedure to "collect" all the values for
use in the Median function, before calling the Median function. That
procedure is what you would call in your query.

Regards

Jeff Boyce
<Access MVP>
 
M

MGFoster

Ronny84 said:
I want to add a column to a query to calculate the medians of one field (#
Days Open) in a table.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I found this solution on an MS SQL Server newsgroup (modified for Access
SQL):

From: http://www.aspfaq.com/show.asp?id=2506

Find the median value

When the number of records is an odd number:

SELECT TOP 1 splunge FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge
)
ORDER BY splunge DESC


When the number of records is an even number. Can be used for odd count
also.

SELECT AVG(splunge) FROM
(
SELECT splunge FROM (
SELECT TOP 1 splunge * 1.0 As splunge FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge
) As sub_a
ORDER BY 1 DESC
) As sub_1
UNION ALL
SELECT splunge FROM (
SELECT TOP 1 splunge * 1.0 As splunge FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge DESC
) AS sub_b
ORDER BY 1
) As sub_2
) As median

Change the names of columns & tables to meet your needs.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQwovaIechKqOuFEgEQJaPgCgvfxVBQuNbZVVeHzjna/LrFJkooMAoOP2
hUKPaLmCVeGSlVNJ8K58eXjO
=BWiK
-----END PGP SIGNATURE-----
 
Top