Solved

How to calculate 25th percentile on an Access report

Posted on 2003-12-09
16
2,103 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

803 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