Solved

Percentile 99 and Percentile 01

Posted on 2003-10-31
3
351 Views
Last Modified: 2012-05-04
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
Comment
Question by:tmagesh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 8

Expert Comment

by:HaydnH
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

by:
HaydnH earned 125 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

by:stevbe
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}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

stevbe
EE Cleanup Volunteer
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question