Percentile calculations in MS Access
Posted on 2007-07-26
I am working with calculating percentiles in MS Access 2003. I have been able to set up a module that calculates any percentile from any field of data in any table.
percentile("water","temp",".25") will calculate the 25th percentile of temperature data located in a table called "water".
I use this module in an expression within a query to get the result.
My problem arises when I attempt to group the results by, let's say, the time of day the water temperature was measured. My attempt at this has been to add another field to "Group By" in the query. I add the "time" field and choose "Group By". The expression continues to calculate the 25th percentile of ALL of the temperature data and does NOT group by the "time" field. The query returns the SAME percentile value (the value that represents the entire field of data) for each time specified through the "Group By".
I have also tried using "Criteria" to select out a subset of my data to perform the calculation, but this does not work either, the expression still returns the value based on the full set of data.
I am thinking that I can make this happen by modifying the SQL (copied here):
SELECT percentile("water","temp",".25") AS Expr1, water.time
GROUP BY water.time;
Any suggestions of what can be done would be great!