Stuck - Median and Quartiles

  • Thread starter Thread starter ScottMsp
  • Start date Start date
S

ScottMsp

Median and Quartiles:

Thanks for reading this. I have read most/all of the posts out there re:
Median and Quartiles and I have attempted a variety of the recommendations
however I have not been able to get them to work and so I am posting my
question on the board. I am using Access 2007. I have some/little knowledge
of VBA and SQL.

I have a table tEmployeeMaster and three of the fields are:
JobCode (text field)
PersonID (text field)
HourlyRate (currency field)

I need to be able to calculate the 1st, 2nd (Median), and 3rd quartiles of
HourlyRate based on the job code.

If I have the following table data:

JobCode PersonID HourlyRate
01 532 10
01 4685 11
01 1256 12
01 9856 13
02 7813 5
02 6523 6
02 9568 7
02 1245 8

I need a query to produce the 1st, 2nd (Median), and 3rd quartile for each
job code

JobCode Q1 Q2 (Median) Q3
01 10.75 11.5 12.25
02 5.75 6.5 7.25
And so on…

I have approximately 20,000 records and the query results should have
approximately 1,800 different JobCodes and their corresponding statistics.

Thanks in advance for your help.

-ScottMSP
 
For Mike Walsh (a.k.a Vanderghast?)

I have been trying to crack the quartile issue within Access and posted my
original question below. I have a few questions based on a VBA you posted.

Issue One:
I tried your solution (pasted below). It worked for my entire dataset
(20,000 rows) and gave me the correct answer (based on Excel's answer),
however I need to have the quartiles for each job code (see original question
below) so that I see in a final query/form each job code and the quartile
next to each job code.

I am not sure what next step I need to make. I am definitely a newbie.

Issue Two:
The VBA takes a long time to run/calculate. Is there another way of doing
this in order to speed up the calculations? My plan would be to use this
code to run the 25th (0.25), 50th (.50) and 75th (.75) Percentiles at the
same time. I cannot imagine how long that will take and thus hoping to find
a faster way of doing this.

Issue Three:
I have been trying to only post on Microsoft's Discussion board, but somehow
my questions also show up on Access Monster. I am not sure where I should be
looking for responses to my question or where I should be posting my
questions.

Thanks in advance for your patiences and help.

Mike's Original Formula
Public Function XPercentile(FName As String, _
TName As String, _
X As Double) _
As Double
' FName = Field name
' TName = Table name
' x = decimal percentile (0.68 for 68%)

' Return the minimum value for which x% of
' the values are lower or equal to it
XPercentile = DMin(FName, TName, _
"DCount(""*"", """ & TName & """, """ & FName & _
"<="" & [" & FName & " ]) >= " & _
X * DCount("*", TName))
End Function
 
Issue 1: It would be a matter to add an extra criteria for the third
argument of the DMin and the two DCounts. Then end result would be quite
slow, though. And the expression will be horrible and hard to maintain. Also
note that

X * DCount("*", TName)

should be replace with

INT(X * DCount("*", TName))

if your regional setting uses a coma rather than a dot as decimal delimiter.



Issue two: Indeed, that would be slow. Even using a recordset would be
quite slow too, since you will have to open an (ordered and dynamic)
recordset for each 'group' . I plan to post a solution based on ranking
(within the various groups), which should be fast, even with large table,
and involving a (one) temporary table. Since it implies multiple steps, I
plan to package it inside a single VBA procedure.


Issue three: There should be NO problem. That is part of the nntp internet
protocol to republish a 'news' already posted on another news server. There
is no wrong doing from your part.


I post my solution as soon as I am satisfied with it, with the definition
you supplied (rather than the one I used, ie, the minimum value in the
sampling where at least x% of the values from the sampling are less than or
equal to that minimum value).


Vanderghast, Access MVP



ScottMsp said:
For Mike Walsh (a.k.a Vanderghast?)

I have been trying to crack the quartile issue within Access and posted my
original question below. I have a few questions based on a VBA you
posted.

Issue One:
I tried your solution (pasted below). It worked for my entire dataset
(20,000 rows) and gave me the correct answer (based on Excel's answer),
however I need to have the quartiles for each job code (see original
question
below) so that I see in a final query/form each job code and the quartile
next to each job code.

I am not sure what next step I need to make. I am definitely a newbie.

Issue Two:
The VBA takes a long time to run/calculate. Is there another way of doing
this in order to speed up the calculations? My plan would be to use this
code to run the 25th (0.25), 50th (.50) and 75th (.75) Percentiles at the
same time. I cannot imagine how long that will take and thus hoping to
find
a faster way of doing this.

Issue Three:
I have been trying to only post on Microsoft's Discussion board, but
somehow
my questions also show up on Access Monster. I am not sure where I should
be
looking for responses to my question or where I should be posting my
questions.

Thanks in advance for your patiences and help.

Mike's Original Formula
Public Function XPercentile(FName As String, _
TName As String, _
X As Double) _
As Double
' FName = Field name
' TName = Table name
' x = decimal percentile (0.68 for 68%)

' Return the minimum value for which x% of
' the values are lower or equal to it
XPercentile = DMin(FName, TName, _
"DCount(""*"", """ & TName & """, """ & FName & _
"<="" & [" & FName & " ]) >= " & _
X * DCount("*", TName))
End Function

ScottMsp said:
Median and Quartiles:

Thanks for reading this. I have read most/all of the posts out there re:
Median and Quartiles and I have attempted a variety of the
recommendations
however I have not been able to get them to work and so I am posting my
question on the board. I am using Access 2007. I have some/little
knowledge
of VBA and SQL.

I have a table tEmployeeMaster and three of the fields are:
JobCode (text field)
PersonID (text field)
HourlyRate (currency field)

I need to be able to calculate the 1st, 2nd (Median), and 3rd quartiles
of
HourlyRate based on the job code.

If I have the following table data:

JobCode PersonID HourlyRate
01 532 10
01 4685 11
01 1256 12
01 9856 13
02 7813 5
02 6523 6
02 9568 7
02 1245 8

I need a query to produce the 1st, 2nd (Median), and 3rd quartile for
each
job code

JobCode Q1 Q2 (Median) Q3
01 10.75 11.5 12.25
02 5.75 6.5 7.25
And so on…

I have approximately 20,000 records and the query results should have
approximately 1,800 different JobCodes and their corresponding
statistics.

Thanks in advance for your help.

-ScottMSP
Click to expand...
Click to expand...
 
To whom it may concern,

HELP! I have tried to set this up in my access database (MS Access 2007). I have set the function up as a module - is that correct? Whether it's a module or just a function, it doesn't work.

When I run my query, it states, "Undefined function "XPercentile" in expression".

I have a table called "DaysforTrustApproval_forIQR" with two fields: LocationName and Days_for_Trust_Approval. I want to know the IQR for each LocationName

I have the following code in a module (called XPercentile):

' ***************Code start**************
' This code was originally written by Michel Walsh.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code courtesy of
' Michel Walsh
'
Public Function XPercentile(Days_for_Trust_Approval As String, _
DaysforTrustApproval_forIQR As String, _
X As Double) _
As Double
' FName = Field name
' TName = Table name
' x = decimal percentile (0.68 for 68%)

' Return the minimum value for which x% of
' the values are lower or equal to it
XPercentile = DMin(Days_for_Trust_Approval, DaysforTrustApproval_forIQR, _
"DCount(""*"", """ & DaysforTrustApproval_forIQR & """, """ & Days_for_Trust_Approval & _
"<="" & [" & Days_for_Trust_Approval & " ]) >= " & _
X * DCount("*", DaysforTrustApproval_forIQR))
End Function
' ***************Code start**************



Any ideas?

I have extremely limited Access programming experience and would love to get some help!

Many thanks,
Sally
Median and Quartiles:

Thanks for reading this. I have read most/all of the posts out there re:
Median and Quartiles and I have attempted a variety of the recommendations
however I have not been able to get them to work and so I am posting my
question on the board. I am using Access 2007. I have some/little knowledge
of VBA and SQL.

I have a table tEmployeeMaster and three of the fields are:
JobCode (text field)
PersonID (text field)
HourlyRate (currency field)

I need to be able to calculate the 1st, 2nd (Median), and 3rd quartiles of
HourlyRate based on the job code.

If I have the following table data:

JobCode PersonID HourlyRate
01 532 10
01 4685 11
01 1256 12
01 9856 13
02 7813 5
02 6523 6
02 9568 7
02 1245 8

I need a query to produce the 1st, 2nd (Median), and 3rd quartile for each
job code

JobCode Q1 Q2 (Median) Q3
01 10.75 11.5 12.25
02 5.75 6.5 7.25
And so on???

I have approximately 20,000 records and the query results should have
approximately 1,800 different JobCodes and their corresponding statistics.

Thanks in advance for your help.

-ScottMSP
On Monday, December 28, 2009 7:45 PM ScottMsp wrote:
For Mike Walsh (a.k.a Vanderghast?)

I have been trying to crack the quartile issue within Access and posted my
original question below. I have a few questions based on a VBA you posted.

Issue One:
I tried your solution (pasted below). It worked for my entire dataset
(20,000 rows) and gave me the correct answer (based on Excel's answer),
however I need to have the quartiles for each job code (see original question
below) so that I see in a final query/form each job code and the quartile
next to each job code.

I am not sure what next step I need to make. I am definitely a newbie.

Issue Two:
The VBA takes a long time to run/calculate. Is there another way of doing
this in order to speed up the calculations? My plan would be to use this
code to run the 25th (0.25), 50th (.50) and 75th (.75) Percentiles at the
same time. I cannot imagine how long that will take and thus hoping to find
a faster way of doing this.

Issue Three:
I have been trying to only post on Microsoft's Discussion board, but somehow
my questions also show up on Access Monster. I am not sure where I should be
looking for responses to my question or where I should be posting my
questions.

Thanks in advance for your patiences and help.

Mike's Original Formula
Public Function XPercentile(FName As String, _
TName As String, _
X As Double) _
As Double
' FName = Field name
' TName = Table name
' x = decimal percentile (0.68 for 68%)

' Return the minimum value for which x% of
' the values are lower or equal to it
XPercentile = DMin(FName, TName, _
"DCount(""*"", """ & TName & """, """ & FName & _
"<="" & [" & FName & " ]) >= " & _
X * DCount("*", TName))
End Function

"ScottMsp" wrote:
Click to expand...
Click to expand...
 
Back
Top