Displaying a Percentile Value Grouped by a separate Field in MS Access

[JDVdb]

I am a novice database user. I am analyzing a data set that is compiled monthly and I would like to use a SQL query to return multiple percentile values that are calculated / grouped by month.

I was able to find a SQL query provided in a previous thread that will give me a single percentile value for the entire dataset but not grouped by month.

SELECT Max([Value]) AS Percentile
FROM (SELECT TOP 50 PERCENT [Value]
FROM [Table/Query]
WHERE [Value] IS NOT NULL
ORDER BY [Value] ASC) AS [TemporaryTableName];

The above works great but returns a single value

Percentile
50th%Value

The output I am striving for is :

Month | 25thPercentile | 75thPercentile
02/2017 | 25th%Value for 02/2017 | 75th%Value for 02/2017
03/2017 | 25th%Value for 03/2017 | 75th%Value for 03/2017
04/2017 | 25th%Value for 04/2017 | 75th%Value for 04/2017

Any help would be greatly appreciated!!

[PhilOfWalton]

It would help if we had details of all the fields in your relevant tables

Phil

[JDVdb]

Assume the fields are set up in a table as follows :

ID | Month | Usage

with some ID's having no Usage in a given month and with Usage having a minimum value of 1 and no maximum, creating a non-normal distribution with a very long tail. I have tried creating a function to calculate what I want using VBA but my data set has 900,000+ rows and the function works but does not perform well. So I am trying to understand if my desired output of displaying percentile rankings of aggregate usage by month can be achieved using an SQL query.

[PhilOfWalton]

I'm not very familiar with percentiles, but this may help

I have a table of Payments

PaymentID         Autonumber
PaymentDate       Date
PaymentAmount     Currency
 

My first query QSubPercentile is

SELECT Month([PaymentDate]) AS Mnth, Payments.PaymentAmount, 
(SELECT Count(*) FROM Payments As U 
WHERE [PaymentAmount] < [Payments].[PaymentAmount])+1 AS Rank, 
(SELECT Count(*) FROM Payments) AS RCount, ([RCount]-[Rank])/[RCount] AS Percentile
FROM Payments
WHERE (((Year([PaymentDate]))=2010))
ORDER BY Payments.PaymentAmount;
 

Note, I limited it to 1 year otherwise there would have been confusion over months.

The second query QPercentile is

SELECT QSubPercentile.Mnth, Max(QSubPercentile.PaymentAmount) AS MaxOfPaymentAmount
FROM QSubPercentile
WHERE (((QSubPercentile.Percentile)<=[Input Percentile]))
GROUP BY QSubPercentile.Mnth
ORDER BY QSubPercentile.Mnth;
 
 

Note that you seem to have to put the figure in with a leading 0 (0.75 not .75)

I have no idea whether this is giving the correct results.

Phil