[Solved] MS Access, get quartiles(percentage)

by


You don’t provide any sample data or expected results but – as the function can return one value only – you will most likely have to use the function in a subquery which you join to the main query on the field you group by in the main query. This way you will filter on the value you group by in the main query.

This will give you the requested Quartile(s). Min, Max, and Median can be obtained with the native function of Access.

Edit

Actually, it could be something like this:

SELECT 
    GroupByField, 
    GetQuartile("YourTable","ValueField",1,"GroupByField='" & [GroupByField] & "'") AS Q1, 
    GetQuartile("YourTable","ValueField",2,"GroupByField='" & [GroupByField] & "'") AS Q2, 
    GetQuartile("YourTable","ValueField",3,"GroupByField='" & [GroupByField] & "'") AS Q3, 
      (Select Avg(ValueField) 
      From YourTable As T 
      Where T.GroupByField = YourTable.GroupByField;) AS 
    AvgValue
  FROM 
    YourTable
  GROUP BY 
    GroupByField;

3

solved MS Access, get quartiles(percentage)