Link to home
Start Free TrialLog in
Avatar of majervis
majervisFlag for United States of America

asked on

Microsoft Access-Grouping records and getting stats (percentile rankings, averages, etc)

I have let's say 1,000 records with the following fields - portfolio name, date, peer group, and number of stocks.  I want to create a query that groups the data set by peer group and report the 95th, 75th, 50th, 25th and 5th percentile of the number of stocks by peer group and date.

I know this is possible by grouping in a query and hitting the average button when grouping by peer group and date for averages, but how do I do this by percentile ranking?

Attached is a sample list of records for illustration.  
percentile-formula-in-excel.xls
Avatar of miqrogroove
miqrogroove
Flag of United States of America image

Percentile usually means rank divided by population.  So you need to query the rank of each item and population of each group, although I'm not sure yet exactly how this context applies to your data.  Population can be easily returned using the count() query function.

Here's a Microsoft article about ranking in queries:

http://support.microsoft.com/kb/208946

If you need any help interpreting that let me know.
ASKER CERTIFIED SOLUTION
Avatar of miqrogroove
miqrogroove
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of majervis

ASKER

I really haven't had the time to test your solution but it looks like you had a grasp of what I was looking.  In the end, I did something similar with averages and standard deviations which were off the shelf and helped me find outlier in my database.  Thank you for your post!
I'm glad this helped.  The difference between using averages and percentiles, of course, is that the percentile calculations would not assume a standard distribution exists.  Enjoy.