Solved

How to calculate 25th percentile on an Access report

Posted on 2003-12-09
16
2,094 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 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

17 Experts available now in Live!

Get 1:1 Help Now