Hi,
replace
INT((q1.rank-1)/4)+1
with
INT(4*(q1.rank-1)/(SELECT MAX(q1.rank)))+1
at both two occurrences.
The idea is to produce a value giving the quartile (1, 2, 3 or 4) the
rank makes the values to belong to.
Hoping it may help,
Vanderghast, Access MVP
"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
news:(E-Mail Removed)...
> Hi,
>
>
>
>
> SELECT a.value, COUNT(*) As rank
> FROM myTable As a INNER JOIN myTable As b
> ON a.value <= b.value
> GROUP BY a.value
>
> would rank the values (First, second, 3, 4, ....., N ). If you save that
> query, say under the name Q1, then
>
>
>
> SELECT INT( (q1.rank-1)/4 )+1 As Quartile,
> MIN(q1.value) As lowestValueInQuartile,
> MAX(q1.value) As highestValueInQuartile
> FROM q1
> GROUP BY INT( (q1.rank-1)/4 )+1
>
>
> that would return the minimum and maximum value for each quartile
>
>
>
> Hoping it may help,
> Vanderghast, Access MVP
>
>
>
>
> "Mark" <(E-Mail Removed)> wrote in message
> news:aad601c4366f$8ee04a30$(E-Mail Removed)...
> > Hello,
> >
> > Is there a way of calculating quartiles (value of a field
> > at each 25% of observations when sorted into ascending
> > order) in a query expression?
> >
> > Excel can do this with the "Quartile" function but Access
> > does not seem to support this.
> >
> > At the moment I am hard coding the values of the quartiles
> > into my query expression like this:
> >
> > Quartile: IIf([tblStore].[SumOfValue]<=438771,"Low",IIf
> > ([tblStore].[SumOfValue]<=1645333,"Medium",IIf([tblStore].
> > [SumOfValue]<=4840280,"High",IIf([tblStore].[SumOfValue]
> > <=23730230,"Very High","Error"))))
> >
> > The 4 numbers in this expression are the values of the
> > quartiles as calculated in Excel, but I need a more
> > dynamic way of doing it so that if the underlying data
> > changes so will my quartile values.
> >
> > Thank you for any help in advance.
> >
> > Mark
>
>
|
Similar Threads
