Solved

How to calculate 25th percentile on an Access report

Posted on 2003-12-09
16
2,116 Views
Last Modified: 2012-06-21
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
Comment
Question by:jblasco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 
LVL 19

Expert Comment

by:Dexstar
ID: 9905735
@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
 

Author Comment

by:jblasco
ID: 9910672
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
 
LVL 19

Expert Comment

by:Dexstar
ID: 9913718
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:jblasco
ID: 9914718
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
 

Author Comment

by:jblasco
ID: 9943020
Can anybody help with this?
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9943324
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
 

Author Comment

by:jblasco
ID: 9973052
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
 
LVL 19

Expert Comment

by:Dexstar
ID: 9973468
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
 

Author Comment

by:jblasco
ID: 9986707
I´m afraid it HAS to be a parameter query.
I will appreciate your "fine tuning" and guidance with that code!
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9986725
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
 

Author Comment

by:jblasco
ID: 10011330
It has 1 parameter: [TherapeuticGroupID]
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 10011393
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
 

Author Comment

by:jblasco
ID: 10015221
"Run time Error 3265
Item not found in this collection"

If I debug it takes me to:

qdfParmQry(strParam) = varValue
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 10016948
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
 

Author Comment

by:jblasco
ID: 10020826
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
 
LVL 19

Accepted Solution

by:
Dexstar earned 400 total points
ID: 10021565
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

Featured Post

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.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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