Percentile calculation in Access

[riaane]

Hey clever people, Excel has the =PERCENTILE function. This calulates a percentile value from an array of values. Is there any equivalent in Access?

[ADezii]

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.

[ADezii]

Back again, decided to let Excel do all the work:

'Make sure to 1st set a Reference to the
'Microsoft Excel XX.X Object Library
Dim intNumbers(3) As Integer
Dim intCounter As Integer
Dim objExcel As Excel.Application
Const conPercentile As Single = 0.25       'must be >=0 And <=1
 
Set objExcel = CreateObject("Excel.Application")
 
'Assign values to the Array
intNumbers(0) = 4
intNumbers(1) = 2
intNumbers(2) = 1
intNumbers(3) = 3
 
'Pass the Array and Percentile to Excel's Percentile() Function
Debug.Print "And the Percentile is: " & objExcel.Application.Percentile(intNumbers(), conPercentile)
 
objExcel.Quit
Set objExcel = Nothing
 

OUTPUT:

And the Percentile is: 1.75

[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 :-)

[ADezii]

[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:

[Percentile]:fCalculatePercentile([<Array Field>])

I do not have Access 2007, but when I get a chance I'll post soome parallel code.

[riaane]

[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

[ADezii]

[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:

'Make sure to 1st set a Reference to the
'Microsoft Excel XX.X Object Library
Dim sngNumbers(21) As Single
Dim objExcel As Excel.Application
Const conPERCENTILE As Single = 0.85       'must be >=0 And <=1
 
Set objExcel = CreateObject("Excel.Application")
 
'Assign values to the Array
sngNumbers(0) = 107.5
sngNumbers(1) = 94.6
sngNumbers(2) = 73.2
sngNumbers(3) = 73.2
sngNumbers(4) = 73.4
sngNumbers(5) = 87.7
sngNumbers(6) = 79.3
sngNumbers(7) = 73.1
sngNumbers(8) = 73.3
sngNumbers(9) = 73.3
sngNumbers(10) = 104.9
sngNumbers(11) = 86.7
sngNumbers(12) = 73.2
sngNumbers(13) = 85.8
sngNumbers(14) = 73.4
sngNumbers(15) = 99.7
sngNumbers(16) = 74
sngNumbers(17) = 85.9
sngNumbers(18) = 73.2
sngNumbers(19) = 73.5
sngNumbers(20) = 73.2
 
'Pass the Array and Percentile to Excel's Percentile() Function
Debug.Print "And the [" & conPERCENTILE & "] Percentile is: " & _
             objExcel.Application.Percentile(sngNumbers(), conPERCENTILE)
 
objExcel.Quit
Set objExcel = Nothing
 

OUTPUT:

And the [0.85] Percentile is: 93.5650016999252

[riaane]

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"

[ADezii]

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?

[riaane]

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.

[ADezii]

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):
    

    Public Function fCalculatePercentile() 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
    Const conPERCENTILE As Single = 0.85       'must be >=0 And <=1
     
    Set MyDB = CurrentDb()
    Set rstPercentile = MyDB.OpenRecordset("tblLogData", 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) = ![OffenseSpeed]
        .MoveNext
      End With
    Next
     
    'Pass the Array to Excel's Percentile Function and Round the result to 2 Decimal Places
    fCalculatePercentile = Round(objExcel.Application.Percentile(sngNumbers(), conPERCENTILE), 2)
     
    'Don't forget to clean up!!!
    rstPercentile.Close
    Set rstPercentile = Nothing
    objExcel.Quit
    Set objExcel = Nothing
    End Function

11. Sample Call to fCalculatePercentile():
    

    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()

[riaane]

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? ;-)

[ADezii]

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.

Set MyDB = CurrentDb()

[riaane]

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

[NeoPa]

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 :

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.

[ADezii]

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.

[riaane]

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

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?

[NeoPa]

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.

[riaane]

Duplication - deleted

[riaane]

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?

[NeoPa]

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.

[ADezii]

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.

[riaane]

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.

[ADezii]

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.

[riaane]

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?

[NeoPa]

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

[riaane]

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

[ADezii]

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)!

[NeoPa]

A Thread-Hijack has been moved off to Percentile Usage.

Please continue that discussion there only please.