QUARTILES in SQL/QUERY

  • Thread starter Thread starter BlueWolverine
  • Start date Start date
B

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.
 
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.
 
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
 
Back
Top