troubleshooting Question

Percentile calculations in MS Access

Avatar of dougf1r
dougf1r asked on
23 Comments1 Solution3729 ViewsLast Modified:
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.

For example:

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
FROM water
GROUP BY water.time;

Any suggestions of what can be done would be great!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 23 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 23 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros