Creating a new field with Percentile Group

KevinC

unread,

Feb 7, 2008, 8:36:29 PM2/7/08

to

Hello All,

I appologies if this is a repertition of questions posted before. I
have been searching all day, I believe I have found some solutions but
they seem to be a little out of my league in terms of interpretation.

Here goes....

I have a list of values in an access table called SAMPLE e.g.

ID VALUES
1 0.235270025
2 0.259548309
3 0.110686103
4 0.375206126
5 0.895241205
6 0.264604818
7 0.703599764
8 0.534924374
9 0.14665753
10 0.310450599
11 0.365349671
12 0.642109156
13 0.418433029
14 0.18319999
15 0.101514293
16 0.719263841
17 0.257357493
18 0.513394362
(etc...)
(etc...)
(etc...)

What I would like to do is create a third column that will show if the
value is in the 10th percentile, 20th percentile, 30th percentile etc.

I am sure this should be quite a simple solution - however some of the
solutions I have seen appear to apply VBA code - unfortubately this is
where I get lost.

If anyone has a solution could they please explain how I link the VBA
code in with a query.

Many thanks in advance.

Kevin

CDMAP...@fortunejames.com

unread,

Feb 8, 2008, 1:31:45 AM2/8/08

to

Try:

qryDecadePercentiles:
SELECT ID, Values, -10 * Int(-10 * (SELECT Count(A.Values) FROM SAMPLE
AS A WHERE A.Values <= SAMPLE.Values) / (SELECT Count(Values) FROM
SAMPLE)) & "th percentile" AS Percentile FROM SAMPLE;

The query uses a subquery to find the percentage of 'Values' values in
the table that are less than or equal to the given 'Values' value,
then uses a common ceiling function, -10 * Int(-X/10), to round up to
the nearest 10. The 100 used to calculate the percentage was reduced
to 10 by the X/10 part of the ceiling function. If the table is large
enough to drag down performance it might be necessary to use VBA
instead of SQL.

James A. Fortune
CDMAP...@FortuneJames.com

KevinC

unread,

Feb 8, 2008, 8:41:23 AM2/8/08

to

Hi James,

Thanks for replying.

I seem to be having problems.

I created a new select query in Access, switched to SQL view and
pasted the code you supplied. However, when I try to run this query I
get the parameter dialog pop up asking me to "Enter Parameter Value"
for "A.Value". It appears that I am doing something wrong here.

Any pointers?

Thanks again.

Kevin

CDMAP...@fortunejames.com

unread,

Feb 8, 2008, 3:38:10 PM2/8/08

to

I copied the SQL directly from a test database so I know it worked for
the table I used. I also tried copying the SQL from the newsgroup
posting and it preserved all the text. Maybe you accidentally deleted
an 's' after pasting the SQL so that A.Values became A.Value. Also,
double check that your table is named SAMPLE and contains fields
called ID and VALUES like you showed in your post.

James A. Fortune
CDMAP...@FortuneJames.com

KevinC

unread,

Feb 9, 2008, 7:19:28 AM2/9/08

to

> CDMAPos...@FortuneJames.com

Thats embarasing - it was a typo.

Thanks for this!

KevinC

unread,

Feb 21, 2008, 5:37:33 PM2/21/08

to

Hi James,

How would I modify the query to find 5% percentile groups instead of
10% groups?

CDMAP...@fortunejames.com

unread,

Feb 23, 2008, 11:20:08 PM2/23/08

to

Try:

qry5PctPercentiles:
SELECT ID, Values, 5 AS PercentileGroup, -PercentileGroup * Int(-100.0/
PercentileGroup * (SELECT Count(A.Values) FROM SAMPLE AS A WHERE

A.Values <= SAMPLE.Values) / (SELECT Count(Values) FROM SAMPLE)) &
"th percentile" AS Percentile FROM SAMPLE;

James A. Fortune
CDMAP...@FortuneJames.com