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

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
• 3
• 2
• 2
• +1
2 Solutions

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

Author 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 JobNum As Integer
Dim P25 As Integer
Dim P50 As Integer
Dim P75 As Integer
Set cnn = CurrentProject.Connection

For JobNum = 1 To 7

strSQL = "SELECT tblJobInformation.SurveyTitleId, tblJobInformation.MinPay " & _
"FROM tblJobInformation " & _
"WHERE (((tblJobInformation.SurveyTitleId)= " & JobNum & ") and (tbljobinformation.minpay is not null)"

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

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

Commented:
In:

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

I believe rst() should be rst(i)
0

Author 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

Commented:
damn, i have an exampple of this at work!  Ill see if i can find it
0

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

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

End Function

Dave
0

Author 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 JobNum As Integer
Dim P25 As Integer
Dim P50 As Integer
Dim P75 As Integer
Set cnn = CurrentProject.Connection

For JobNum = 1 To 7

strSQL = "SELECT tblJobInformation.SurveyTitleId, tblJobInformation.MinPay " & _
"FROM tblJobInformation " & _
"WHERE (((tblJobInformation.SurveyTitleId)= " & JobNum & ") and (tbljobinformation.minpay is not null))"

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

For JobNum = 1 To 7

strSQL = "SELECT qryReportData.SurveyTitleId, qryReportData.MidPay " & _
"FROM qryReportData " & _
"WHERE (((qryReportData.SurveyTitleId)= " & JobNum & ") and (qryReportData.midpay is not null))"

ReDim intArray(rst.RecordCount - 1)

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

rst.Close

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

For JobNum = 1 To 7

strSQL = "SELECT tblJobInformation.SurveyTitleId, tblJobInformation.MaxPay " & _
"FROM tblJobInformation " & _
"WHERE (((tblJobInformation.SurveyTitleId)= " & JobNum & ") and (tbljobinformation.Maxpay is not null))"

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