Solved

Using Excel's percentile function

Posted on 2004-08-13
8
2,719 Views
Last Modified: 2008-01-09
I have a function that allows me to call Excel's percentile function. I can use it in queries and in modules as well. It's set up like this: Percentile(Array,%).

I can use it easily in Modules by building arrays of values I want certain percentile ranks for, but I'm confused as to how it might be used in Access's query builder. What would go in as the Array argument if I wanted to calculate a certain percentile rank for a collection of records?

I doubt I've provided enough information to be informative, but I'm not sure what other information to provide.

Thanks for the help.
0
Comment
Question by:Feyo
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 17

Expert Comment

by:walterecook
ID: 11795764
So the real question, then is how do you pass the array to the Percentile function.  Can you show us how you build the array?

Walt
0
 
LVL 4

Author Comment

by:Feyo
ID: 11795930
I haven't finished with this code, but here it is:

Sub percentile()

Dim intArray() As Double
Dim i As Integer
Dim strSQL As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim JobNum As Integer
Dim P25 As Integer
Dim P50 As Integer
Dim P75 As Integer
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

For JobNum = 1 To 7

    strSQL = "SELECT tblJobInformation.SurveyTitleId, tblJobInformation.MinPay " & _
    "FROM tblJobInformation " & _
    "WHERE (((tblJobInformation.SurveyTitleId)= " & JobNum & ") and (tbljobinformation.minpay is not null)"
   
    rst.Open "strSQL", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   
    ReDim intArray(rst.RecordCount - 1)
   
    For i = 1 To rst.RecordCount
        intArray(i - 1) = rst()
    rst.MoveNext
    Next i
   
    P25 = Excel.Application.WorksheetFunction.percentile(intArray, 0.25)
    P50 = Excel.Application.WorksheetFunction.percentile(intArray, 0.5)
    P75 = Excel.Application.WorksheetFunction.percentile(intArray, 0.75)
   
Next JobNum

End Sub

What I have to do next is write the different percentiles to a table so that I have the 25th, 50th, and 75 percentiles for minpay. I just thought if I could use this function in the query builder that it would be easier.

Thanks

0
 
LVL 17

Assisted Solution

by:walterecook
walterecook earned 100 total points
ID: 11796100
To return a number to a query, you'll need to convert it to a Function and not a sub.  Then return a value to it.
if you put something like this:
somevalue: percentile()

It'll call that function and display the return.  BE CAREFUL because that will make the code run for every line in your recordset.
You could have that in it's own query, might be one idea.

Walt
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11796472
In:

    For i = 1 To rst.RecordCount
        intArray(i - 1) = rst()
    rst.MoveNext
    Next i

I believe rst() should be rst(i)
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 4

Author Comment

by:Feyo
ID: 11797509
GRayL-Yeah I screwed that up. Thanks.

Walt - I did write a function called Percentile. I can call that function from the query builder, but I don't know how to fill the array argument for the function in the query builder.

Percentile(Array,.25) - what would serve as the Array argument in the query builder? Can I use an SQL statement?

Thanks
0
 
LVL 34

Expert Comment

by:flavo
ID: 11798035
damn, i have an exampple of this at work!  Ill see if i can find it
0
 
LVL 34

Accepted Solution

by:
flavo earned 150 total points
ID: 11798040
found it, its a little old i think, but i tmay get you going at least

>> argument for the function in the query builder.

I dont think you can do this in Access, if you can i'd love to know how to, saves my temp table method.

heres my code: - bulit for A97 so it uses DAO.

you can an excel function like this.  Youll need to refrence the Excel library (In VBA window, "Tools", "Refrences", then select "Microsoft Excel....." - same goes for Microsoft DAO Object Library 3.??

Function myPercentile(myArray, myValue As Double) As Double
 'use excel percentile funciton mayhems
 myPercentile = Excel.WorksheetFunction.Percentile(myArray, myValue)
End Function

' ********** And here's how i use it

Function PercentileInAccess() As Double
'dont forget to refrence Excel and DAO
Dim intArray() As Double
Dim i As Integer
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database
'open connection
Set dbs = CurrentDb

strSQL = "SELECT tblValues.Value FROM tblValues;" 'select all values in "fake" table to test
'set the recordset we want (all values)
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
'need to populate the record set so we can get the right count
rst.MoveFirst
rst.MoveLast
'make sure we are at the first record
rst.MoveFirst
 'initalise the array with the number of entries in the recordset
ReDim intArray(rst.RecordCount - 1)
 'looping mayhems - add the record data into the array
 For i = 1 To rst.RecordCount
   intArray(i - 1) = rst(0)
   rst.MoveNext
 Next

'display the answer...
'MsgBox myPercentile(intArray, 0.85) 'change 0.85 to relfect other percentiles you wish to calculate

End Function


Dave
0
 
LVL 4

Author Comment

by:Feyo
ID: 11821885
Well, I opted to do things another way. I fill some tables with the percentiles I want, then use them from there. It's simple and straightforward. I do appreciate your help, so I'm gonna divide points between those who responded with solutions. Thanks.

If you are interested, here's the code I'm using

Sub percentile()

Dim intArray() As Double
Dim i As Integer
Dim strSQL As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim JobNum As Integer
Dim P25 As Integer
Dim P50 As Integer
Dim P75 As Integer
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
Set rst2 = New ADODB.Recordset

'Adding records to tblMinPercentiles
For JobNum = 1 To 7

    strSQL = "SELECT tblJobInformation.SurveyTitleId, tblJobInformation.MinPay " & _
    "FROM tblJobInformation " & _
    "WHERE (((tblJobInformation.SurveyTitleId)= " & JobNum & ") and (tbljobinformation.minpay is not null))"
   
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Debug.Print rst.RecordCount
    ReDim intArray(rst.RecordCount - 1)
   
    For i = 1 To rst.RecordCount
        intArray(i - 1) = rst(1)
    rst.MoveNext
    Next i
   
    rst.Close
   
    rst2.Open "tblMinPercentiles", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   
    rst2.AddNew
        rst2.Fields(0) = JobNum
        rst2.Fields(1) = Excel.Application.WorksheetFunction.percentile(intArray, 0.25)
        rst2.Fields(2) = Excel.Application.WorksheetFunction.percentile(intArray, 0.5)
        rst2.Fields(3) = Excel.Application.WorksheetFunction.percentile(intArray, 0.75)
    rst2.Update
   
    rst2.Close
   
Next JobNum

'Adding records to tblMidPercentiles
For JobNum = 1 To 7

    strSQL = "SELECT qryReportData.SurveyTitleId, qryReportData.MidPay " & _
    "FROM qryReportData " & _
    "WHERE (((qryReportData.SurveyTitleId)= " & JobNum & ") and (qryReportData.midpay is not null))"
   
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   
    ReDim intArray(rst.RecordCount - 1)
   
    For i = 1 To rst.RecordCount
        intArray(i - 1) = rst(1)
    rst.MoveNext
    Next i
   
    rst.Close
   
    rst2.Open "tblmidPercentiles", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   
    rst2.AddNew
        rst2.Fields(0) = JobNum
        rst2.Fields(1) = Excel.Application.WorksheetFunction.percentile(intArray, 0.25)
        rst2.Fields(2) = Excel.Application.WorksheetFunction.percentile(intArray, 0.5)
        rst2.Fields(3) = Excel.Application.WorksheetFunction.percentile(intArray, 0.75)
    rst2.Update
   
    rst2.Close
   
Next JobNum

'Adding records to tblMaxPercentiles
For JobNum = 1 To 7

    strSQL = "SELECT tblJobInformation.SurveyTitleId, tblJobInformation.MaxPay " & _
    "FROM tblJobInformation " & _
    "WHERE (((tblJobInformation.SurveyTitleId)= " & JobNum & ") and (tbljobinformation.Maxpay is not null))"
   
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Debug.Print rst.RecordCount
    ReDim intArray(rst.RecordCount - 1)
   
    For i = 1 To rst.RecordCount
        intArray(i - 1) = rst(1)
    rst.MoveNext
    Next i
   
    rst.Close
   
    rst2.Open "tblMaxPercentiles", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   
    rst2.AddNew
        rst2.Fields(0) = JobNum
        rst2.Fields(1) = Excel.Application.WorksheetFunction.percentile(intArray, 0.25)
        rst2.Fields(2) = Excel.Application.WorksheetFunction.percentile(intArray, 0.5)
        rst2.Fields(3) = Excel.Application.WorksheetFunction.percentile(intArray, 0.75)
    rst2.Update
   
    rst2.Close
   
Next JobNum
End Sub
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now