Is there a way to get 25th/75th Percentile in Access Query?

[FBandData]

I am trying to get the 25th and 75th percentile of a table with a group by

Select Category, Price
From ProductsTable
Group By Category

What can I do to get 25th Percentile of Price within each Category? There will be over a hundred categories so if there is a better way to do the same thing, that would be great.

I just need results like this:
Category 25thPercPrice 75thPercPrice
Tickets $30 $50

 

[Micron]

I'm not a statistcian by any means, but I think your best bet would be to do this using a form. You'd need a textbox to hold the count of your list items and something for the percentile you want to use (maybe a combo with some percentile values in a value list). When the form opens, set the textbox to the count, choose a percentile and click a button to do the math in a function. If you're using the nearest rank method, the function would calculate the ordinal position as (I think)
OP = percentile/100*listcount. I would use a query as a table to better ensure the list is in ascending order (no guarantee that a table sort will be 100% accurate, regardless of how you sort it) and go to the nth record from it using a sql statement like

SELECT TOP 1 * FROM
(SELECT TOP OP qryThatSorts.*
FROM qryThatSorts
ORDER BY qryThatSorts.SortingField)
order by SomeOrderIfNeeded

Obviously, you replace my made up names with your own. If you don't want all the fields (SELECT TOP 1 * FROM) you modify that part of the sql.

 

[FBandData]

Thanks... but I am not sure I understand how you are doing this with all 100+ categories since I don't see a group by in it. I am looking to find a query based solution that would provide the percentile for all categories. I am not sure why Access doesn't have this option when in Excel, it would be possible to do an array with a Percentile IF.

 

[Micron]

You probably know more about Excel for this than I so if it's easier, might be better off working with the results afterwards in Access if necessary. As for the 100 catagories, I envisioned the first query would provide 100 groups along with the count per group. As for why, Access is more about data storage than computation. Math/statistics sometimes better left to Excel IMHO.

 

[FBandData]

Thanks, but I am looking to do it via Query and not with Excel because Excel doesn't handle doing hundreds of arrays well. It taxes the resources.

 

[Micron]

If you want help, I can only see a VB solution. It would be something like query1 groups by category and returns count of category, then do the math using the count and the percentile number you want (e.g.25), and call the result the ordinal position. Then run a query on the underlying products table using the category as criteria, and go to the Nth (ordinal position) in that recordset and get the dollar amount (or whatever it is you need) from that record row. Just about anything is possible in Access as long as you can code it and know the underlying premise. As for me, I'm not too bad with code, but as you probably noticed, I can't spell statistician.

Forgot to mention that all of this would be done in a loop that runs as many times as there are category rows in the first query.