Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Percentile 99 and Percentile 01

Posted on 2003-10-31
Medium Priority
357 Views
How do I calculate the Percentile 99 and Percentile 01 in the Access Database? I see that we have the PERCENTILE worksheet function, but it takes an array of a maximum of 8191 data points. My need is to calculate the P99 and P01 for more than 60000 data points.
0
Question by:tmagesh
• 2

LVL 8

Expert Comment

ID: 9657670
You can list the top N % using the TOP predicate in SQL... So to calculate the top 1% you would use:

SELECT TOP 1 PERCENT ScoreField FROM MyTable ORDER BY Score;

And to list the bottom 1% you would use:

SELECT TOP 1 PERCENT ScoreField FROM MyTable ORDER BY Score DESC;

Haydn.
0

LVL 8

Accepted Solution

HaydnH earned 375 total points
ID: 9657806
Sorry, those 2 are the wrong way around - include the DESC to get the TOP 1%, exclude the DESC for the bottom 1%

Haydn.
0

LVL 39

Expert Comment

ID: 10012498
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: HaydnH {http:#9657806}

stevbe
EE Cleanup Volunteer
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
###### Suggested Courses
Course of the Month10 days, 8 hours left to enroll