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

Percentile calculation in Access

P: 33
Hey clever people, Excel has the =PERCENTILE function. This calulates a percentile value from an array of values. Is there any equivalent in Access?
Sep 10 '08 #1
Share this Question
Share on Google+
28 Replies


ADezii
Expert 5K+
P: 8,627
There is no equivalent in Access, but if I get a chance I'll see if I can create an Access Version for you, or use Automation Code to obtain a Percentile for Access data using the actual Excel Function.
Sep 10 '08 #2

ADezii
Expert 5K+
P: 8,627
Back again, decided to let Excel do all the work:
Expand|Select|Wrap|Line Numbers
  1. 'Make sure to 1st set a Reference to the
  2. 'Microsoft Excel XX.X Object Library
  3. Dim intNumbers(3) As Integer
  4. Dim intCounter As Integer
  5. Dim objExcel As Excel.Application
  6. Const conPercentile As Single = 0.25       'must be >=0 And <=1
  7.  
  8. Set objExcel = CreateObject("Excel.Application")
  9.  
  10. 'Assign values to the Array
  11. intNumbers(0) = 4
  12. intNumbers(1) = 2
  13. intNumbers(2) = 1
  14. intNumbers(3) = 3
  15.  
  16. 'Pass the Array and Percentile to Excel's Percentile() Function
  17. Debug.Print "And the Percentile is: " & objExcel.Application.Percentile(intNumbers(), conPercentile)
  18.  
  19. objExcel.Quit
  20. Set objExcel = Nothing
  21.  
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. And the Percentile is: 1.75
Sep 10 '08 #3

P: 33
Whoa ADezii, you are way above my skill level here *sheepish* I am not a programmer in any shape or form and use Access 2007 only as a tool to help me consolidate statistics.

1. I assume the code you list is VB that has to be included in a function of the Data control of the field I require the calculation on?

2. My array source is a CSV file generated by a system of my client and can consist of anything between 20 and 2000 records, each record containing 8 fields, of which I need the 85th Percentile of only 1 of those fields. Therefore your step "10. 'Assign values to the Array" is actually already done by importing this file into my Access DB and running queries and reports to end up with a consolidated fiew of the original data and is therefore not a manual process. I assume this step will be replaced by referring to my table and exporting that table (or maybe just the relevant field I need the Percentile calculation on) to Excel?

Thank you for taking the time to reply to my request :-)
Sep 12 '08 #4

ADezii
Expert 5K+
P: 8,627
[quote=riaane]Whoa ADezii, you are way above my skill level here *sheepish* I am not a programmer in any shape or form and use Access 2007 only as a tool to help me consolidate statistics.

1. I assume the code you list is VB that has to be included in a function of the Data control of the field I require the calculation on?

2. My array source is a CSV file generated by a system of my client and can consist of anything between 20 and 2000 records, each record containing 8 fields, of which I need the 85th Percentile of only 1 of those fields. Therefore your step "10. 'Assign values to the Array" is actually already done by importing this file into my Access DB and running queries and reports to end up with a consolidated fiew of the original data and is therefore not a manual process. I assume this step will be replaced by referring to my table and exporting that table (or maybe just the relevant field I need the Percentile calculation on) to Excel?

Thank you for taking the time to reply to my request :-)
I need to see what the 'finalized' data looks like in your Table, specifically the one Field on which you would like to calculate the 85th Percentile. I'm a little confused as to how this Array is stored in your Table (Complex Data Type?). The Table need not be Exported to Excel. You can basically create a Recordset based on this Table, and for each Record calculate the 85th Percentile for the specific Field in question. This calculation can easily be done via a Calculate Field in a Query which calls the actual Function that performs the calculation. The Field containing the Array Data is passed as an Argument, something similar to:
Expand|Select|Wrap|Line Numbers
  1. [Percentile]:fCalculatePercentile([<Array Field>])
I do not have Access 2007, but when I get a chance I'll post soome parallel code.
Sep 12 '08 #5

P: 33
[quote=ADezii]

I need to see what the 'finalized' data looks like in your Table, specifically the one Field on which you would like to calculate the 85th Percentile. I'm a little confused as to how this Array is stored in your Table (Complex Data Type?).
Hi ADezii, here is a sample if the data in the field I need the 85th Percentile calculated from:

107.5
94.6
73.2
73.2
73.4
87.7
79.3
73.1
73.3
73.3
104.9
86.7
73.2
85.8
73.4
99.7
74
85.9
73.2
73.5
73.2

The 85th Percentile value for this array btw = 94.6

Hope this helps
Sep 12 '08 #6

ADezii
Expert 5K+
P: 8,627
[quote=riaane]
Hi ADezii, here is a sample if the data in the field I need the 85th Percentile calculated from:

107.5
94.6
73.2
73.2
73.4
87.7
79.3
73.1
73.3
73.3
104.9
86.7
73.2
85.8
73.4
99.7
74
85.9
73.2
73.5
73.2

The 85th Percentile value for this array btw = 94.6

Hope this helps
I ran your Data set through my Algorithm, and the result is as posted below. I'll also post the revised code for your convenience. I still need to see the physical layout of these Values in the Field in your Table, so I can design a Method to extract them, and run them through Excel's Percentile Function:
Expand|Select|Wrap|Line Numbers
  1. 'Make sure to 1st set a Reference to the
  2. 'Microsoft Excel XX.X Object Library
  3. Dim sngNumbers(21) As Single
  4. Dim objExcel As Excel.Application
  5. Const conPERCENTILE As Single = 0.85       'must be >=0 And <=1
  6.  
  7. Set objExcel = CreateObject("Excel.Application")
  8.  
  9. 'Assign values to the Array
  10. sngNumbers(0) = 107.5
  11. sngNumbers(1) = 94.6
  12. sngNumbers(2) = 73.2
  13. sngNumbers(3) = 73.2
  14. sngNumbers(4) = 73.4
  15. sngNumbers(5) = 87.7
  16. sngNumbers(6) = 79.3
  17. sngNumbers(7) = 73.1
  18. sngNumbers(8) = 73.3
  19. sngNumbers(9) = 73.3
  20. sngNumbers(10) = 104.9
  21. sngNumbers(11) = 86.7
  22. sngNumbers(12) = 73.2
  23. sngNumbers(13) = 85.8
  24. sngNumbers(14) = 73.4
  25. sngNumbers(15) = 99.7
  26. sngNumbers(16) = 74
  27. sngNumbers(17) = 85.9
  28. sngNumbers(18) = 73.2
  29. sngNumbers(19) = 73.5
  30. sngNumbers(20) = 73.2
  31.  
  32. 'Pass the Array and Percentile to Excel's Percentile() Function
  33. Debug.Print "And the [" & conPERCENTILE & "] Percentile is: " & _
  34.              objExcel.Application.Percentile(sngNumbers(), conPERCENTILE)
  35.  
  36. objExcel.Quit
  37. Set objExcel = Nothing
  38.  
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. And the [0.85] Percentile is: 93.5650016999252
Sep 12 '08 #7

P: 33
Dear ADezii,

Please see a screen of the table:
Table Laayout

I only need the calculation on the OffenceSpeed field

I hope this answers your query "I still need to see the physical layout of these Values in the Field in your Table, so I can design a Method to extract them, and run them through Excel's Percentile Function"
Sep 13 '08 #8

ADezii
Expert 5K+
P: 8,627
Dear ADezii,

Please see a screen of the table:
Table Laayout

I only need the calculation on the OffenceSpeed field

I hope this answers your query "I still need to see the physical layout of these Values in the Field in your Table, so I can design a Method to extract them, and run them through Excel's Percentile Function"
Just a couple more questions:
  • How are the Grouping Levels defined, namely the 21 Values of 107.5 to 73.2?
  • Is the .85 Percentile to be calculated for all Values in the OffenceSpeed Field in tblLogData?
Sep 13 '08 #9

P: 33
Just a couple more questions:
  • [1] How are the Grouping Levels defined, namely the 21 Values of 107.5 to 73.2?

    [2] Is the .85 Percentile to be calculated for all Values in the OffenceSpeed Field in tblLogData?
1. The Grouping levels are for Max, Count and Avg
2. Yes, the calculation should be for all Values in the OffenceSpeed field only. Take note that this field can consist of anything between 200 and 2000 record entries as the different files imported into this table has different number of records every time. So the 21 values shown in the screen is only a sample of the 258 records in the table for that import. Therefore there should not be a hard coded definition of the number of records to be used in the Percentile calculation. Obviously the values differ every time as well so the values has to come from the field ([OffenceSpeed])
Please see the form I need the final calculation to apear on.
Sep 13 '08 #10

ADezii
Expert 5K+
P: 8,627
1. The Grouping levels are for Max, Count and Avg
2. Yes, the calculation should be for all Values in the OffenceSpeed field only. Take note that this field can consist of anything between 200 and 2000 record entries as the different files imported into this table has different number of records every time. So the 21 values shown in the screen is only a sample of the 258 records in the table for that import. Therefore there should not be a hard coded definition of the number of records to be used in the Percentile calculation. Obviously the values differ every time as well so the values has to come from the field ([OffenceSpeed])
Please see the form I need the final calculation to apear on.
The following code will calculate the .85 Percentile for all values entered into the [OffenseSpeed] Field, rounded to 2 Decimal places. The actual number of entries should make no difference, since the code is flexible enough to adapt for varying Record Counts. I'll post the code and a sample Call below, but first some very importatnt points to mention:
  1. The code is based on a Field named [OffenseSpeed] in a Table named tblLogData. If this is not accurate, change the Table and/or Field Name in the code.
  2. You must set a Reference to the Microsoft Excel XX.X Object Library.
  3. [OffenseSpeed] must be a Numeric Data Type, specifically {SINGLE}.
  4. [OffenseSpeed] 'must' be a Required Field or Errors will result.
  5. The code calculates the .85 Percentile, to modify the Percentile, change the Value of the Constant.
  6. The Function (fCalculatePercentile) must be Declared as Public in a Standard Code Module.
  7. The result of the Function Call is rounded to 2 Decimal Places. To modify this, change the 2nd Argument of the Round() Function within the Function.
  8. Any questions, feel free to ask, but I think everything was pretty much covered. Let me know how you make out.
  9. Just for curiosity, what is the purpose of this Percentile calculation, if it can be revealed?
  10. After a long drum roll, here is the Function (tested and operational):
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalculatePercentile() 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. Const conPERCENTILE As Single = 0.85       'must be >=0 And <=1
    11.  
    12. Set MyDB = CurrentDb()
    13. Set rstPercentile = MyDB.OpenRecordset("tblLogData", dbOpenSnapshot)
    14. rstPercentile.MoveLast: rstPercentile.MoveFirst     'accurate Record Count
    15.  
    16. intNumberOfRecords = rstPercentile.RecordCount
    17.  
    18. 'Redimension the Array to proper values
    19. ReDim sngNumbers(1 To intNumberOfRecords)
    20.  
    21. Set objExcel = CreateObject("Excel.Application")
    22.  
    23. 'Populate the Array with values from the Recordset
    24. For intCounter = 1 To intNumberOfRecords
    25.   With rstPercentile
    26.     sngNumbers(intCounter) = ![OffenseSpeed]
    27.     .MoveNext
    28.   End With
    29. Next
    30.  
    31. 'Pass the Array to Excel's Percentile Function and Round the result to 2 Decimal Places
    32. fCalculatePercentile = Round(objExcel.Application.Percentile(sngNumbers(), conPERCENTILE), 2)
    33.  
    34. 'Don't forget to clean up!!!
    35. rstPercentile.Close
    36. Set rstPercentile = Nothing
    37. objExcel.Quit
    38. Set objExcel = Nothing
    39. End Function
  11. Sample Call to fCalculatePercentile():
    Expand|Select|Wrap|Line Numbers
    1. MsgBox "The .85 Percentile equals: " & fCalculatePercentile()
P.S. - To have the result appear in a Text Box on your Form, set the Control Source of the Text Box on the Form equal to the Function, namely: =fCalculatePercentile()
Sep 13 '08 #11

P: 33
Dear ADezii, thank you so much for all your patience and effort. You are a saint! :-)
I ran your code but get a Compile Error even though I have my Reference set correctly.

What did I break?

"Just for curiosity, what is the purpose of this Percentile calculation, if it can be revealed?"

The 85th Percentile calculation is an international benchmark used by Speed Law Enforcement Agencies to decide from what speed upwards speed law enforcement should happen. In simplified terms, it basically states that 85% of motorists drive at or below this speed on that particular road. That gives a statistical indication of the safe speed limit for that streach of road as the general assumption is that most motorists act as responsible and reasonable drivers for the prevailing road conditions, road surface and road type (mountain pass, sharp bends, streight sections, etc).

The manual way to calculate this value is to fill out a form of a sample of 125 vehicles trevelling past an observer and then filling in each vehicle speed in the relevent speed increment. More detail on the methodology can be found here .

See why I rather want my PC to calculate this? ;-)
Sep 13 '08 #12

ADezii
Expert 5K+
P: 8,627
Dear ADezii, thank you so much for all your patience and effort. You are a saint! :-)
I ran your code but get a Compile Error even though I have my Reference set correctly.

What did I break?

"Just for curiosity, what is the purpose of this Percentile calculation, if it can be revealed?"

The 85th Percentile calculation is an international benchmark used by Speed Law Enforcement Agencies to decide from what speed upwards speed law enforcement should happen. In simplified terms, it basically states that 85% of motorists drive at or below this speed on that particular road. That gives a statistical indication of the safe speed limit for that streach of road as the general assumption is that most motorists act as responsible and reasonable drivers for the prevailing road conditions, road surface and road type (mountain pass, sharp bends, streight sections, etc).

The manual way to calculate this value is to fill out a form of a sample of 125 vehicles trevelling past an observer and then filling in each vehicle speed in the relevent speed increment. More detail on the methodology can be found here .

See why I rather want my PC to calculate this? ;-)
Code Line #12 is exactly as stated, don't change it, it will automatically set a Reference to the Current Database.
Expand|Select|Wrap|Line Numbers
  1. Set MyDB = CurrentDb()
Sep 13 '08 #13

P: 33
Hmmm, thats what I thought but I had the code as you proposed originally and got the same error, so then tried to change the DB ref...
Sep 14 '08 #14

NeoPa
Expert Mod 15k+
P: 31,429
I'm coming to this without having read the full text of the thread so far, so forgive me if I'm off-base here.

I just thought I'd mention though, that the Excel functionality is available to you from within Access using :
Expand|Select|Wrap|Line Numbers
  1. Excel.WorksheetFunction.Percentile()
To enable this availability it is necessary to add a reference to Excel in your project. To do this got to the Access VBA Editor window (Alt-F11 from Access) then open the References window (Tools / References...) and check the item for the latest version of Excel you have installed.

If this is not helpful then please ignore.
Sep 15 '08 #15

ADezii
Expert 5K+
P: 8,627
Hmmm, thats what I thought but I had the code as you proposed originally and got the same error, so then tried to change the DB ref...
rianne, from your Error Link it appears that you are attempting to execute this code from the General Declarations Section of a Report Module with the initial Function Line missing. As previously stated, this Function must reside in a Standard Code Module and must be Public. This code has been tested many times, and is in fact fully operational. I also strongly suggest you go over each ad every point listed in Post #11. NeoPa's reference is also listed as Item #2.

P.S. - Download the Attachment, it will help you. You may have to change the Reference to the Microsoft Excel XX.X Object Library if yours does not coincide with mine.
Sep 15 '08 #16

P: 33
rianne, from your Error Link it appears that you are attempting to execute this code from the General Declarations Section of a Report Module with the initial Function Line missing. As previously stated, this Function must reside in a Standard Code Module and must be Public. This code has been tested many times, and is in fact fully operational. I also strongly suggest you go over each ad every point listed in Post #11. NeoPa's reference is also listed as Item #2.

P.S. - Download the Attachment, it will help you. You may have to change the Reference to the Microsoft Excel XX.X Object Library if yours does not coincide with mine.
ADezii and NeoPa, thanks for the assistance. Sorry for taking so long to reply but was a little busy with other things.

Regarding the Reference Library you are both alluding to, please see my post #12 above. Or am I misunderstanding what you guys are talking about when referring to "reference"

ADezii, thank you so much for all your patience. My code works now (three cheers for that!!) :-)

NeoPa, I will try your suggestion of
Expand|Select|Wrap|Line Numbers
  1. Excel.WorksheetFunction.Percentile()
but as I mentioned at the start, I am not a programmer at all (to the endless frustration of ADezii). Please can you tell be if the code string you mentioned can be used used in the Data>Control Source>Expression Builder of the field in my Report or do I have to involve VB (Alt-F11) again?
Sep 17 '08 #17

NeoPa
Expert Mod 15k+
P: 31,429
I can't easily say Riaane, as I don't have a situation with any data I can test with.

However, you should be able to test it easily enough simply by trying it out in your database. Let us know what you find.
Sep 17 '08 #18

P: 33
Duplication - deleted
Sep 17 '08 #19

P: 33
NeoPa, attached is a sample with data to use. The code you supplied is not sufficient as it has no expressions and I am not sure where to define the data set that should be used for the calculation.

With the DB below, can you give me some pointers please?
Attached Files
File Type: zip NewDigicamStats_NewVersion.zip (51.1 KB, 171 views)
Sep 17 '08 #20

NeoPa
Expert Mod 15k+
P: 31,429
I'm afraid I can't be much help there Riaane :(

I was simply introducing you to the concept of running general Excel worksheet functions from within Access. I haven't really much experience processing statistical data in Excel or Access. I thought I could leave that part to you.

NB. No database currently attached to your post.
Sep 17 '08 #21

ADezii
Expert 5K+
P: 8,627
NeoPa, attached is a sample with data to use. The code you supplied is not sufficient as it has no expressions and I am not sure where to define the data set that should be used for the calculation.

With the DB below, can you give me some pointers please?
Back from vacation, riaane, and so nice of you to greet me this way! (LOL). I've downloaded the Attachment and will take it into work tomorrow where, hopefully, I will have a chance to take a close look at it. I will probably get back to you in a day or two. If the Attachment is truly representational of what you are working with, I should be able to locate the problem. Be patient.
Sep 18 '08 #22

P: 33
If the Attachment is truly representational of what you are working with, I should be able to locate the problem.
The attachment is my entire DB for this project. Humble but there you have it ;-)

There are intricacies on the files that are imported as the original data but that is not relevant for your testing, unless you really want to be bored with the details.
Sep 18 '08 #23

ADezii
Expert 5K+
P: 8,627
The attachment is my entire DB for this project. Humble but there you have it ;-)

There are intricacies on the files that are imported as the original data but that is not relevant for your testing, unless you really want to be bored with the details.
riaane, I downloaded the Attachment and ran the Digicam Session Report and all went well once I corrected the Excel Reference. The code works as intended and it is located where it should be. The only think that I can see that may be interfering with the code execution is the Required References. You must have References to:
  1. Microsoft Excel XX.X Object Library
  2. Microsoft Access DAO X.X Object Library
  3. In any Code Window select Tools ==> References, then make sure that these two Libraries appear in the Window and are not absent or MISSING.
Sep 18 '08 #24

P: 33
The only think that I can see that may be interfering with the code execution is the Required References. You must have References to:
  1. Microsoft Excel XX.X Object Library
  2. Microsoft Access DAO X.X Object Library
  3. In any Code Window select Tools ==> References, then make sure that these two Libraries appear in the Window and are not absent or MISSING.
ADezii, now I am lost.

Regarding the "Microsoft Excel XX.X Object Library", what do you mean ? Surely this is correct as I have indicated before?

Regarding "Microsoft Access DAO X.X Object Library", the closest reference I can get to this is here and then get the accompanying error when trying to activate it.

Am I smoking my socks here?
Sep 19 '08 #25

NeoPa
Expert Mod 15k+
P: 31,429
...
Regarding the "Microsoft Excel XX.X Object Library", what do you mean ? Surely this is correct as I have indicated before?
...
Definitely.
...
Regarding "Microsoft Access DAO X.X Object Library", the closest reference I can get to this is here and then get the accompanying error when trying to activate it.

Am I smoking my socks here?
I would be looking at the one just below the one selected.

Let's stop it with the socks now - I can hardly breathe :D
Sep 19 '08 #26

P: 33
Definitely.

I would be looking at the one just below the one selected.

Let's stop it with the socks now - I can hardly breathe :D
hehe ok I included this library. Thanks so much for al you help and patience
Sep 19 '08 #27

ADezii
Expert 5K+
P: 8,627
ADezii, now I am lost.

Regarding the "Microsoft Excel XX.X Object Library", what do you mean ? Surely this is correct as I have indicated before?

Regarding "Microsoft Access DAO X.X Object Library", the closest reference I can get to this is here and then get the accompanying error when trying to activate it.

Am I smoking my socks here?
Regarding "Microsoft Access DAO X.X Object Library", the closest reference I can get to this is here and then get the accompanying error when trying to activate it.
As indicated by NeoPa, select the Microsoft DAO 3.51 Object Library, and I do believe you'll have a Winner!

P.S. - I also agree with NeoPa on this one, lose the socks! (LOL)!
Sep 19 '08 #28

NeoPa
Expert Mod 15k+
P: 31,429
A Thread-Hijack has been moved off to Percentile Usage.

Please continue that discussion there only please.
May 23 '09 #29

Post your reply

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