Solved

Using Excel's percentile function

Posted on 2004-08-13
8
2,723 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

896 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

11 Experts available now in Live!

Get 1:1 Help Now