By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

Percentile Usage

P: 12
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.
May 22 '09 #1
Share this Question
Share on Google+
19 Replies


ADezii
Expert 5K+
P: 8,679
@SachinPatel
  1. Copy and Paste the following 'Public' Function to a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalculatePercentiles(sngPercentile As Single)
    2. 'Make sure to 1st set a Reference to the
    3. 'Microsoft Excel XX.X Object Library
    4. Dim sngNumbers() As Single
    5. Dim intNumberOfRecords As Integer
    6. Dim objExcel As Excel.Application
    7. Dim intCounter As Integer
    8. Dim MyDB As DAO.Database
    9. Dim rstPercentile As DAO.Recordset
    10.  
    11. Set MyDB = CurrentDb()
    12. Set rstPercentile = MyDB.OpenRecordset("TBL_DATA", dbOpenSnapshot)
    13. rstPercentile.MoveLast: rstPercentile.MoveFirst     'accurate Record Count
    14.  
    15. intNumberOfRecords = rstPercentile.RecordCount
    16.  
    17. 'Redimension the Array to proper values
    18. ReDim sngNumbers(1 To intNumberOfRecords)
    19.  
    20. Set objExcel = CreateObject("Excel.Application")
    21.  
    22. 'Populate the Array with values from the Recordset
    23. For intCounter = 1 To intNumberOfRecords
    24.   With rstPercentile
    25.     sngNumbers(intCounter) = ![Days]
    26.     .MoveNext
    27.   End With
    28. Next
    29.  
    30. 'Pass the Array to Excel's Percentile Function and Round the result to 2 Decimal Places
    31. fCalculatePercentiles = Round(objExcel.Application.Percentile(sngNumbers(), sngPercentile), 2)
    32.  
    33. 'Don't forget to clean up!!!
    34. rstPercentile.Close
    35. Set rstPercentile = Nothing
    36. objExcel.Quit
    37. Set objExcel = Nothing
    38. End Function
  2. Execute the following SQL Statement given your Table and Field Names:
    Expand|Select|Wrap|Line Numbers
    1. SELECT Min(TBL_DATA.Days) AS MinOfDays, Max(TBL_DATA.Days) AS MaxOfDays, Avg(TBL_DATA.Days) AS AvgOfDays, 
    2. fCalculatePercentiles(0.95) AS [95th Percentile], 
    3. fCalculatePercentiles(0.75) AS [75th Percentile], 
    4. fCalculatePercentiles(0.5) AS [50th Percentile], 
    5. fCalculatePercentiles(0.25) AS [25th Percentile]
    6. FROM TBL_DATA
    7. 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!
May 23 '09 #2

P: 12
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?
May 23 '09 #3

ADezii
Expert 5K+
P: 8,679
@SachinPatel
As previously stated in Post #30, Item 1, it must be defined as 'Public' in a Standard Code Module.
May 23 '09 #4

P: 12
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?
May 23 '09 #5

ADezii
Expert 5K+
P: 8,679
@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.
May 23 '09 #6

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

thanks again your a star!
May 23 '09 #7

ADezii
Expert 5K+
P: 8,679
@SachinPatel
Just in case you didn't notice the previous Post, I was able to create the Attachment in work. Good Luck.
May 23 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
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 ;)
May 23 '09 #9

P: 12
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
May 23 '09 #10

ADezii
Expert 5K+
P: 8,679
@SachinPatel
See fdor yourself in Post #6 of this Thread.
May 23 '09 #11

P: 12
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!
May 24 '09 #12

P: 12
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?
May 24 '09 #13

P: 12
sorry - its not that i have foudn the way to get the heading as modules and still not referring to module!
May 24 '09 #14

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 24 '09 #15

ADezii
Expert 5K+
P: 8,679
@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.
May 24 '09 #16

NeoPa
Expert Mod 15k+
P: 31,709
8-)

Thanks for posting to clarify :)
May 24 '09 #17

FishVal
Expert 2.5K+
P: 2,653
Alternatively, percentile could be calculated using SQL facilities only.
The following example gives 90th percentile of [Value] field in [tbl]

Expand|Select|Wrap|Line Numbers
  1. SELECT Max([Value]) AS Percentile
  2. FROM 
  3. (SELECT TOP 90 PERCENT [Value]
  4. FROM [tbl]
  5. ORDER BY [Value] ASC);
  6.  
May 25 '09 #18

ADezii
Expert 5K+
P: 8,679
@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!
Expand|Select|Wrap|Line Numbers
  1. Percentiles      95th        75th        50th        25th
  2. FishVal          100          88          67          21
  3. Excel            101.15       88.25       67          22.5
  4.  
P.S. - Results compiled with Sample Data listed below (Excel results rounded to 2 Decimal Places):
Expand|Select|Wrap|Line Numbers
  1. 21
  2. 7
  3. 34
  4. 2
  5. 67
  6. 33
  7. 100
  8. 89
  9. 67
  10. 23
  11. 88
  12. 100
  13. 77
  14. 16
  15. 44
  16. 77
  17. 11
  18. 77
  19. 89
  20. 123
May 25 '09 #19

FishVal
Expert 2.5K+
P: 2,653
:)

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.
May 25 '09 #20

Post your reply

Sign in to post your reply or Sign up for a free account.