Table Statistics by Subgroup - Microsoft Access 2016

[lavrentios]

I currently have a table in access with the following form:

GROUP	VALUE
A	1
A	2
A	3
A	4
A	5
B	6
B	7
B	8
B	9
B	10
C	11
C	12
C	13
C	14
C	15

<tbody>
</tbody>

​

I am trying to create a query that will generate the following data broken down at the group level:

GROUP	MEDIAN	1ST QUARTILE	MIN	MAX	3RD QUARTILE
A
B
C

<tbody>
</tbody>

The only luck i have had thus far is to break the data into smaller groups as the calculations see to occur at the aggregate level.

 

[alansidman]

Min and Max can be done in the following manner:

SELECT tblGroup.GROUP, Min(tblGroup.VALUE) AS [Min], Max(tblGroup.VALUE) AS [Max]
FROM tblGroup
GROUP BY tblGroup.GROUP;

Quartiles and Median need to be done with created User Defined Functions. Here are two links you can refer to:

https://msdn.microsoft.com/en-us/library/dd789431(v=office.12).aspx

Queries: Quartiles, Percentiles

 

[lavrentios]

I added the user defined function from the link that you provided and it seems to calculate the quartiles correctly. However, I still can't seem to get it to calculate for each group. Here is a link the a sample access database: https://www.dropbox.com/s/gkj4o1dxmojolwl/test.accdb?dl=0

If you run the "1st Quartile" query you will get the following output:

Group	Q1
A	20
B	20

<tbody>
</tbody>

I currently have the query set to return the 100th percentile (for testing purposes)...it is returning the 100% for all "Number" field values for the entire table. I am trying to get the "A" group 1st Quartile and the "B" group 1st Quartile to calculate independently. With the current setting at 100% i am trying to achieve the following output:

Group	Q1
A	8
B	20

<tbody>
</tbody>