Normally when people ask about getting the Nth percentile from a group,
the response is to write a couple of VBA modules. But it seems to me
there's a simpler way that just uses 2 queries. You might even be able
to get it down to one query if you nest them.

Here's how I get the 25th percentile number from a table, where
mynumber is the field that has the value and mytable is the table name:

top_25_query:
select top 25 percent mynumber from mytable order by mynumber;

25th_percentile_query:
select top 1 mynumber from top_25_query order by mynumber desc;

This seems more portable to me and easier for non-scripters to
understand.

Is there any reason that this would be a wrong way to do it? Will it
give me the wrong answer? It hasn't yet.

Thanks

"jd142" <jd142@hotmail.com> wrote in message
news:1116420807.244659.269310@o13g2000cwo.googlegr oups.com...[color=blue]
> Normally when people ask about getting the Nth percentile from a group,
> the response is to write a couple of VBA modules. But it seems to me
> there's a simpler way that just uses 2 queries. You might even be able
> to get it down to one query if you nest them.
>
> Here's how I get the 25th percentile number from a table, where
> mynumber is the field that has the value and mytable is the table name:
>
> top_25_query:
> select top 25 percent mynumber from mytable order by mynumber;
>
> 25th_percentile_query:
> select top 1 mynumber from top_25_query order by mynumber desc;
>
> This seems more portable to me and easier for non-scripters to
> understand.
>
> Is there any reason that this would be a wrong way to do it? Will it
> give me the wrong answer? It hasn't yet.
>
> Thanks
>[/color]
What if there is more than 1 record with the 25th percentile mynumber?

Since I only what the number, not who has that number, it shouldn't
make a difference.

In other words, I don't care that on their final exam, John, Jane,
Richard, and Alice all scored a 32, I just care that 32 is the score at
the 25th percentile.