QUARTILES in SQL/QUERY

[BlueWolverine]

Hey,
MS Access 2003 in XP PRO.

I'm evaluating personel and I need to divide a rating into quartiles,
preferably in a query, not filling a table tiwh VB, though if that's the only
way, then so be it.

Field 1: Driver ID
FIeld 2: Count of Issues

I want Field 3: to be the quartile of the distribution of the Count of
Issues. I want to be able to pick out top quartile, second quartile, 3rd
quartile, bottom quartile for each driver. I know what I want to do with
that info once I have it, I just can't figure out how to do it.

Thanks.

 

[S.Clark]

Total Access Statistics by FMS can help.
http://www.fmsinc.com/MicrosoftAccess/StatisticalAnalysis.html

 

[BlueWolverine]

I need something free and not an instal. My comp is locked without admin
rights and My company has no money (go figure) for purchasing 3rd party
software.

Thanks.

 

[Michel Walsh]

If you define the x-percentile as the lowest value for which at least x% of
the records have a value less or equal to it, that is, t

he RANK of the record should be >= (x/100) * Number of record


So, using join to compute the rank (assuming there is no duplicated value
for the field used to rank records):


SELECT a.primaryKey, COUNT(*) AS rank
FROM table AS a INNER JOIN table AS b
ON a.fieldToRank >= b.fieldToRank
GROUP BY a.primaryKey


does just that. To get all the records past the x-percentile (we take the
primary key and the value defining the ranking position):


SELECT a.primaryKey, LAST(a.fieldToRank)
FROM table AS a INNER JOIN table AS b
ON a.fieldToRank >= b.fieldToRank
GROUP BY a.primaryKey
HAVING COUNT(*) >= [x] *(SELECT COUNT(*) FROM table)



or to just get the value itself making the 'breaking' point:


SELECT MIN(y.theValue)
FROM (
SELECT LAST(a.fieldToRank) AS theValue
FROM table AS a INNER JOIN table AS b
ON a.fieldToRank >= b.fieldToRank
GROUP BY a.primaryKey
HAVING COUNT(*) >= x *(SELECT COUNT(*) FROM table)
) AS y




(you may have to write that query in two parts: save the inner most query,
then use SELECT MIN(y.theValue) FROM savedQuery As y)




Vanderghast, Access MVP