# Thread: Microsoft Access 2007 Percentile Calculation

1. Registered User
Join Date
May 2009
Posts
17

## Unanswered: Microsoft Access 2007 Percentile Calculation

After days of trying to create a percentile calculation in access i have decided to ask you for help.

i have a table with the following columns in access

Transaction Date
Days until Returns
User ID
Method
Trans ID
Return Date
ReturnReason

I would like to calculate the percentile for a perticular return reason per month by using the Days until Returns column as the interger.

Is their any adivice or sql you can provide which can help me?

Sean

2. Registered User
Join Date
Sep 2002
Location
South Wales
Posts
580
"I would like to calculate the percentile for a perticular return reason per month by using the Days until Returns column as the interger."

Don't quite understand you hear;

You want to show a % for each 'ReturnReason' - do you mean if you had
say 5 people on one category, 5 in another and 40 in a third it would return:

category one 10%
category two 10%
category three 80%

If not, please post sample data and output required.

3. Registered User
Join Date
Sep 2002
Location
South Wales
Posts
580
er...Percentile ! (blush)

In that case - sample data and desired output definately please.

4. Registered User
Join Date
May 2009
Posts
17

## Microsoft Access 2007 Percentile Calculation

Gareth,

thanks for your response - apologise on the confusion hope this clarifies........

if i return reason of "Account closed" which has 8 enteries in the days until return columns

i.e

Return reason Days until returns
Account Closed 21
Account Closed 2
Account Closed 1
Account Closed 34
Account Closed 12
Account Closed 18
Account Closed 12
Account Closed 46

I would like to know the 95th percentile so in the results table of the query i would see a single entry for Account closed with the value for 95th percentile.

in excel all i would do is select all 8 enteries underdays to return and place in percentile function if i used the above 8 numbers in excel and am looking for the 95th percentile the answer would be 41.8

i.e
=PERCENTILE("8 numbers",0.95)

any help?

5. Registered User
Join Date
Sep 2002
Location
South Wales
Posts
580
hang fire...drum roll...

6. Registered User
Join Date
May 2009
Posts
17

7. Registered User
Join Date
Sep 2002
Location
South Wales
Posts
580
Just looking at it now - bet Rudy could write a killer bit of SQL at this point!...

I'm pretty sure this could be done elegantly with subqueries and certainly as a VBA function.

Please be patient - I may not be able to respond immediately!

8. Registered User
Join Date
May 2009
Posts
17
Hey Gareth,

Found this code on internet - not sure if its any help- does it make any sense?

Public Function Percentile(strTbl As String, strFld As String, k As Double)
As Double
Dim dblData() As Double
Dim xl As Object
Dim x As Integer
Set xl = CreateObject("Excel.Application")
rst.Open "Select * from " & strTbl, CurrentProject.Connection,
ReDim dblData(rst.RecordCount - 1)
For x = 0 To (rst.RecordCount - 1)
dblData(x) = rst(strFld)
rst.MoveNext
Next x
Percentile = xl.WorksheetFunction.Percentile(dblData, k)
rst.Close
Set rst = Nothing
Set xl = Nothing
End Function

9. Registered User
Join Date
Sep 2002
Location
South Wales
Posts
580
Hi Sachin,

Yes - this code just automates excel to get the percentile via the application. (Essentially, Excel 'works it out').

However, what I was looking to do was to provide an answer either as;

An SQL statement/s
Or using a VBA function (Without Automating Excel).

<both of which will require a little time (I need to work out how precicely Excel calculates percentiles).

I will post a solution for you but I cannot say exactly when - Best guess is Monday evening given my current workload.

10. Registered User
Join Date
May 2009
Posts
17
Gareth,

your a star - thanks and i look forward to seeing the result query.

11. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by garethdart
bet Rudy could write a killer bit of SQL
Code:
SELECT (MAX([days until returns]) - MIN([days until returns])) * 0.95 AS we_dont_needs_no_rude_boi
FROM myTable

12. Registered User
Join Date
May 2009
Posts
17
Rudy,

Thanks for your response, however that calculation gives us a valueof 42.75 and not the 41.8 that excel gives on the percentile function???

13. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
No you are right - wrong formula. I misread the data, and thought the lowest value was "2", which funnily enough would return the right value.

14. L33t Helpa Munky
Join Date
Nov 2007
Location