Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2787
  • Last Modified:

Using Excel's percentile function

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
Feyo
Asked:
Feyo
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
walterecookCommented:
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
 
FeyoAuthor Commented:
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
 
walterecookCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
GRayLCommented:
In:

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

I believe rst() should be rst(i)
0
 
FeyoAuthor Commented:
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
 
flavoCommented:
damn, i have an exampple of this at work!  Ill see if i can find it
0
 
flavoCommented:
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
 
FeyoAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now