Solved

How to calculate 25th percentile on an Access report

Posted on 2003-12-09
16
2,109 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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

765 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