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]

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.

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 fCalculatePerce ntiles() 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 'fCalculatePerc entiles' in expression

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

[ADezii]

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 'fCalculatePerc entiles' in expression

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

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]

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?

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]

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

thanks again your a star!

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]

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

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 misunderstandin g 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.