Median and Percentile(.95) in a Access Query

[Guest]

Can you calculate Median and Percentile(.95) in a Access Query with a GROUP
BY statement? You can do both of these in Excel Functions but can not find
them in Access?

 

[MGFoster]

Can you calculate Median and Percentile(.95) in a Access Query with a GROUP
BY statement? You can do both of these in Excel Functions but can not find
them in Access?

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

Here is an article describing how to get a statistical median:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210581

In Access 2002 you can use the TOP n PERCENT. E.g.:

SELECT TOP 95 PERCENT * FROM ... etc. ...

See the Access Help article "ALL, DISTINCT, DISTINCTROW, TOP Predicates"
for more info.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQkMRzoechKqOuFEgEQKR+gCdHil+lIlHX7cOnG2Je1r0svgw42QAnRE1
bZkzdtK15CO0dGMhYN+AhaUY
=5K8v
-----END PGP SIGNATURE-----

 

[Michel Walsh]

Hi,


With




PolesAges
Pole Age
AM 2
AM 3
AS 5
AS 7
EU 9
EU 3
EU 2







the query

SELECT c.Pole, Min(c.age) AS MinOfage
FROM (SELECT a.Pole, a.age
FROM PolesAges AS a INNER JOIN PolesAges AS b ON a.Pole=b.Pole AND
a.Age>=b.Age
GROUP BY a.Pole, a.Age
HAVING Count(*) / (SELECT COUNT(*) FROM PolesAges As d WHERE
d.Pole=a.Pole) >=0.5) AS c
GROUP BY c.Pole;




returns




MediansByGroup
Pole MinOfage
AM 2
AS 5
EU 3






which, as you see, previlegiate the lowers values, or, technically, return
the lowest "age" for which at least 50% or more of the record supply a lower
age, given the Pole (GROUP)... and that, including the returned record.




Hoping it may help,
Vanderghast, Access MVP