Percentile Usage

[SachinPatel]

ADezii,

i have been reading your solution to this percentile problem and believe you can help me.

I have a similar problem with creating a percentile in access 2007.

My table is called "TBL_DATA" and the field with all the values i would like to gain a percentile for is called "DAYS"

i want to develop a query which shows the following columns

- Min Days
- Max days
- Avg Days
- Percentile 95th
- Percentile 75th
- Percentile 50th
- Percentile 25th

i have created the first 3 columns in design view however am having problems creating the percentile calculations.

I would like access to look at all values under "DAYS" and bring back the percentile requested.

I am happyusing either a VBA script or a SQL statement as long as it gets the right answer.

Hope you can help, Thanking you in advance.

[ADezii]

@SachinPatel

1. Copy and Paste the following 'Public' Function to a Standard Code Module:
   

   Public Function fCalculatePercentiles(sngPercentile As Single)
   'Make sure to 1st set a Reference to the
   'Microsoft Excel XX.X Object Library
   Dim sngNumbers() As Single
   Dim intNumberOfRecords As Integer
   Dim objExcel As Excel.Application
   Dim intCounter As Integer
   Dim MyDB As DAO.Database
   Dim rstPercentile As DAO.Recordset
    
   Set MyDB = CurrentDb()
   Set rstPercentile = MyDB.OpenRecordset("TBL_DATA", dbOpenSnapshot)
   rstPercentile.MoveLast: rstPercentile.MoveFirst     'accurate Record Count
    
   intNumberOfRecords = rstPercentile.RecordCount
    
   'Redimension the Array to proper values
   ReDim sngNumbers(1 To intNumberOfRecords)
    
   Set objExcel = CreateObject("Excel.Application")
    
   'Populate the Array with values from the Recordset
   For intCounter = 1 To intNumberOfRecords
     With rstPercentile
       sngNumbers(intCounter) = ![Days]
       .MoveNext
     End With
   Next
    
   'Pass the Array to Excel's Percentile Function and Round the result to 2 Decimal Places
   fCalculatePercentiles = Round(objExcel.Application.Percentile(sngNumbers(), sngPercentile), 2)
    
   'Don't forget to clean up!!!
   rstPercentile.Close
   Set rstPercentile = Nothing
   objExcel.Quit
   Set objExcel = Nothing
   End Function

2. Execute the following SQL Statement given your Table and Field Names:
   

   SELECT Min(TBL_DATA.Days) AS MinOfDays, Max(TBL_DATA.Days) AS MaxOfDays, Avg(TBL_DATA.Days) AS AvgOfDays, 
   fCalculatePercentiles(0.95) AS [95th Percentile], 
   fCalculatePercentiles(0.75) AS [75th Percentile], 
   fCalculatePercentiles(0.5) AS [50th Percentile], 
   fCalculatePercentiles(0.25) AS [25th Percentile]
   FROM TBL_DATA
   GROUP BY fCalculatePercentiles(0.25);

3. Note that you are passing the actual Percentile Value to the fCalculatePercentiles() Function within the SQL Statement.
4. Good Luck and Good Night!

[SachinPatel]

ADezil,

sorry to bother you buti have doneas you stated above however when i try run the query i am getting the error message as follows:

Undefined Function 'fCalculatePercentiles' in expression

is there a certain way i shouldbe saving the VBA code to ensure the query can refer to it?

[ADezii]

@SachinPatel
As previously stated in Post #30, Item 1, it must be defined as 'Public' in a Standard Code Module.

[SachinPatel]

Sorry mate - i believe i am defining the item as public, however as this is new to me maybe i am doing it wrong- can you possible tell me the correct way to define the function as public in a standard code module?

[ADezii]

@SachinPatel
I'll do better than that. I'll be in work all day, but when I get home this evening I'll send you an Attachment indicating how, exactly, this is done. Be advised that this has only been tested on an insignificant amount of data, and may be incredibly slow on a large number of Records since you are using 3 Aggregate Functions, as well as several Calls to the same Public Function with different Parameters, within the Query. You may also experience some difficulty since you are essentially making the same Function Calls by passing different values (Percentiles). All the good stuff I'll leave to you - see you later. OK, was able to get it done here.

[SachinPatel]

thanks mate your a life saver - i will log on later (5 hours or so)

thanks again your a star!

[ADezii]

@SachinPatel
Just in case you didn't notice the previous Post, I was able to create the Attachment in work. Good Luck.

[NeoPa]

I have split this away from Percentile calculation in Access as it is a different question.

I know you're very new so we'll let it slide this time, but posting your question in an existing thread is hijacking, and is not allowed on this site.

I don't, for a moment, want to stop you asking questions, simply guiding you do do it the proper way in future.

Welcome to Bytes!

NB. Post #6 has the attachment ADezii promised you. Don't overlook it simply because it's not the latest post ;)

[SachinPatel]

hey ADezii,

just seeing if you had chance to send over how to make a vba function public and refer toit on the sql you created without getting an error message.

Thanks

[ADezii]

@SachinPatel
See fdor yourself in Post #6 of this Thread.

[SachinPatel]

Hey Adezii

thanks for that - wheni open the attachment i get error message saying macros action failed - should this happen?

also i looked at VB and still unable to confirmi if i am correctly creating a public function my SQL query can reference.

NeoPa - apologise for the misunderstanding of how these forums work!

[SachinPatel]

just noticed when i create my module i comes under the heading unrelated objects while yours comes under the modules heading - this could be the reason?

if so how can i do it so it looks like yours and comes under modules heading?

[SachinPatel]

sorry - its not that i have foudn the way to get the heading as modules and still not referring to module!

[NeoPa]

From Access :

1. Alt-F11 to switch to the VB Editor.
2. Ctrl-R for the Project Explorer pane.
3. Right-Click in there and select Insert / Module.
4. Enter your code in here and save it.

[ADezii]

@NeoPa
Hello NeoPa, just wanted to let you know that I managed to send the Attachment to the OP via his Private E-Mail Address and all seems to have worked out well. Thanks.

[NeoPa]

8-)

Thanks for posting to clarify :)

[FishVal]

Alternatively, percentile could be calculated using SQL facilities only.
The following example gives 90th percentile of [Value] field in [tbl]

SELECT Max([Value]) AS Percentile
FROM 
(SELECT TOP 90 PERCENT [Value]
FROM [tbl]
ORDER BY [Value] ASC);
 

[ADezii]

@FishVal
Hell FishVal, just thought that you would find the following interesting in that there is a slight discrepancy between the 2 Methods, we'll simply call it FishVal vs Excel!

Percentiles      95th        75th        50th        25th
FishVal          100          88          67          21
Excel            101.15       88.25       67          22.5
 

P.S. - Results compiled with Sample Data listed below (Excel results rounded to 2 Decimal Places):

21
7
34
2
67
33
100
89
67
23
88
100
77
16
44
77
11
77
89
123

[FishVal]

:)

I know that the results are different.
This happens because dataset is by definition a set of discrete values, so SQL rounds percentile down to existing value while Excel seems to make interpolation.
Wikipedia article says both methods are in use.

Regards,
Fish.

PS: FYI, Excel could operate with up to 8191 values while SQL is limited by RDBMS specifications for max records.