• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2182
  • Last Modified:

How to calculate 25th percentile on an Access report

I have found the following code that should allow me to calculate the 25th percentile of a recordset:
http://www.fabalou.com/Access/Modules/percentile.asp

I have created a module with that code and I am trying to use the PercentileRst function in a report (which is based on a query called Query1), to calculate the 25th percentile of a field called Unit Price. I know nothing about VB or SQL, so I guess my problem is that I'm calling the recordset the wrong way. I am writing the following in the Control source of a text box on the report:
=PercentileRst (XXX, [Unit Price], 0.25)

What should I write instead of that XXX?
0
jblasco
Asked:
jblasco
  • 8
  • 8
1 Solution
 
DexstarCommented:
@jblasco:

> What should I write instead of that XXX?

According to the function definition, it should be the name of the table or query that you want the calculation done on.  Also, I think you need quotes around the field name:

     =PercentileRst("Query1", "[Unit Price]", 0.25)

Hope That Helps,
Dex*
0
 
jblascoAuthor Commented:
Thanks Dex, but it doesn't work, I get the following message:
"Run time error '3061'
Too few parameters. Expected 1"

Debug sends me to the following line in the module:

Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)

Any ideas?
0
 
DexstarCommented:
I don't see how that line pertains to your question.  Could you post the entire function where that line is from, so I can see how they are related?

Dex*
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
jblascoAuthor Commented:
Option Compare Database

Public Function PercentileRst(RstName As String, fldName As String, PercentileValue As Double) As Double
   'This function will calculate the percentile of a recordset.
   'The field must be a number value and the percentile has to
   'be between 0 and 1.
   If PercentileValue < 0 Or PercentileValue > 1 Then
      MsgBox "Percentile must be between 0 and 1", vbOKOnly
   End If
   Dim PercentileTemp As Double
   Dim dbs As Database
   Set dbs = CurrentDb
   Dim xVal As Double
   Dim iRec As Long
   Dim i As Long
   Dim RstOrig As Recordset
   Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
   RstOrig.Sort = fldName
   Dim RstSorted As Recordset
   Set RstSorted = RstOrig.OpenRecordset()
   RstSorted.MoveLast
   RstSorted.MoveFirst
   xVal = ((RstSorted.RecordCount - 1) * PercentileValue) + 1
   'x now contains the record number we are looking for.
   'Note x may not be     whole number
   iRec = Int(xVal)
   xVal = xVal - iRec
   'i now contains first record to look at and
   'x contains diff to next record
   RstSorted.Move iRec - 1
   PercentileTemp = RstSorted(fldName)
   If xVal > 0 Then
      RstSorted.MoveNext
      PercentileTemp = ((RstSorted(fldName) - PercentileTemp) * xVal) + PercentileTemp
   End If
   RstSorted.Close
   RstOrig.Close
   Set RstSorted = Nothing
   Set RstOrig = Nothing
   Set dbs = Nothing
   PercentileRst = PercentileTemp
End Function
0
 
jblascoAuthor Commented:
Can anybody help with this?
0
 
DexstarCommented:
At this line:
     Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)

It is trying to open the table or query with the name "RstName".  What is the value of "RstName"?  It should be "Query1", but only if you have a Query in your DB named "Query1".  If you do have it one, and it still doesn't work,  try changing it to be the name of a table, just to see if that works.

Dex*
0
 
jblascoAuthor Commented:
It seems to work with tables:

=PercentileRst("Name of table", "Name of field", 0.25)

gives me the right output.

Not with queries though, any ideas on how I should call the query and field name to make it work?
0
 
DexstarCommented:
Okay, I think it is because your "Query1" is a parameter query.  You can still do what you want, but it is going to be a little trickier.  Here is how to open a parameter query programmatically:
     http://www.mvps.org/access/queries/qry0003.htm

You're going to have to modify that function to get it to work for you, if you want to accept parameters.  Can you change "Query1" to NOT be a parameter query?  If not, I can help you change the code around so that it will work, but that's more work that I usually put into a 200 pointer... ;)

Dex*
0
 
jblascoAuthor Commented:
I´m afraid it HAS to be a parameter query.
I will appreciate your "fine tuning" and guidance with that code!
0
 
DexstarCommented:
How many parameters does it have?  What is the name of that parameter?

We're going to have to make a separate function that takes an extra parameter: the value of the parameter to pass in.
0
 
jblascoAuthor Commented:
It has 1 parameter: [TherapeuticGroupID]
0
 
DexstarCommented:
Alrighty then... Try this:

Option Compare Database

Public Function PercentileRstParam(RstName As String, strParam as String, varValue as Variant, fldName As String, PercentileValue As Double) As Double
   'This function will calculate the percentile of a recordset.
   'The field must be a number value and the percentile has to
   'be between 0 and 1.
   If PercentileValue < 0 Or PercentileValue > 1 Then
      MsgBox "Percentile must be between 0 and 1", vbOKOnly
   End If

   Dim PercentileTemp As Double
   Dim dbs As Database
   Dim xVal As Double
   Dim iRec As Long
   Dim i As Long
   Dim RstOrig As Recordset
   Dim qdfParmQry As QueryDef

   Set dbs = CurrentDb
   Set qdfParmQry = dbs.QueryDefs(RstName)
   qdfParmQry(strParam) = varValue
   Set RstOrig = qdfParmQry.OpenRecordset()

   RstOrig.Sort = fldName

   Dim RstSorted As Recordset
   Set RstSorted = RstOrig.OpenRecordset()

   RstSorted.MoveLast
   RstSorted.MoveFirst
   xVal = ((RstSorted.RecordCount - 1) * PercentileValue) + 1
   'x now contains the record number we are looking for.
   'Note x may not be     whole number
   iRec = Int(xVal)
   xVal = xVal - iRec
   'i now contains first record to look at and
   'x contains diff to next record
   RstSorted.Move iRec - 1
   PercentileTemp = RstSorted(fldName)
   If xVal > 0 Then
      RstSorted.MoveNext
      PercentileTemp = ((RstSorted(fldName) - PercentileTemp) * xVal) + PercentileTemp
   End If
   RstSorted.Close
   RstOrig.Close
   Set RstSorted = Nothing
   Set RstOrig = Nothing
   Set dbs = Nothing
   PercentileRstParam = PercentileTemp
End Function

Okay, and then instead of this:
     =PercentileRst("Query1", "[Unit Price]", 0.25)

You call it like this:
     =PercentileRstParam("Query1", "[TherapeuticGroupID]", x, "[Unit Price]", 0.25)

(Where "x" is the value for TherapeuticGroupID that you want to use...)

I hope that helps.  Let me know what errors or problems you have with it.

Dex*
0
 
jblascoAuthor Commented:
"Run time Error 3265
Item not found in this collection"

If I debug it takes me to:

qdfParmQry(strParam) = varValue
0
 
DexstarCommented:
Well, then the name of the parameter that I used is wrong.  But I just went by what you told me.  When you go into Access, and open "Query1", it pops up and asks you for a value, right?

What is the EXACT text of it uses in the prompt?  Whatever that is, put it in your call where I put "[TherapeuticGroupID]".  That should fix it.

If you can't get it, then please post the SQL statement for Query1, and I'll see what I can find out from that.

Dex*
0
 
jblascoAuthor Commented:
It works!!

Now the final question:

I am grouping the records on the report by a field called "Drug name". Would it be possible to get that box to calculate the percentile for each individual Drug name instead of the whole range of records of the parameter query?
I have tried to do it by putting the text box at the "Drug name" header on the report, but it still calculates the value for the whole set of records, not individually for each Drug name.
If I  use the "Running sum" property of the text box, the result of the "group" calculations is not correct. Any ideas of how I could get that "grouped" percentile?

Thanks a lot!!
0
 
DexstarCommented:
I didn't write that function, so I'm not sure how it works.  I would go to wherever you got it, and see if they have a version that works for groups.  Sorry.  :(

Dex*
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now