Creating a Field With Percentile Group

[KevinC]

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

 

[KevinC]

Hi again,

I have posted the above question else-where and got the reply from
James below. I thought I would post it here just incase anyone else
found it useful.

I have still been having problems however. So perhaps someone can
help me!

I created a new select query in Access, switched to SQL view and
pasted the code James supplied below. 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

------Start of message----------

 

[John Spencer]

Here is the same query expanded to show how things get calculated.

If you are being asked to supply Value and the field name is Values then I
would suspect an typing error. I built the table as described and ran this
query against the table.

SELECT SAMPLE.ID, SAMPLE.Values
, (SELECT Count(A.Values)
FROM SAMPLE AS A
WHERE A.Values <= SAMPLE.Values) AS Rank
, (SELECT Count(Values)
FROM SAMPLE) AS TotalSamples
, (SELECT Count(A.Values)
FROM SAMPLE AS A
WHERE A.Values <= SAMPLE.Values)/(SELECT Count(Values)
FROM SAMPLE) AS Percentage
, -Int(-10*((SELECT Count(A.Values)
FROM SAMPLE AS A
WHERE A.Values <= SAMPLE.Values)/(SELECT Count(Values)
FROM SAMPLE)))*10 AS Percentile
FROM SAMPLE
ORDER BY SAMPLE.Values



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[KevinC]

Hi John,

How would I modify to select query to find 5 percentile values?

 

[John Spencer]

What do you mean by percentile values? DO you want to specify any five
values such as - 11,14, 22, 81 and 92 or do you mean you want to split
the percentiles into 5 groups instead of 10?

5 Groups instead of 10 should probably be solved by replacing the 10's
in the sample with 5's.

Specifying 5 different break points is something else.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[KevinC]

Sorry, should have made that clearer.

What I wanted to do was the same process but to find which percentile
group each values falls with - so in this case we have identified
which decile groups each value is in I would like the find the bottom
5% and the top 5% and which 5% percentile group each of the values
falls within in all those groups in between.

 

[John Spencer]

So you want to split the groups into 20 groups (100 / 5 = 200). Try the
following and let me know if that gets the results you are looking for.

SELECT SAMPLE.ID, SAMPLE.Values
, (SELECT Count(A.Values)
FROM SAMPLE AS A
WHERE A.Values <= SAMPLE.Values) AS Rank
, (SELECT Count(Values)
FROM SAMPLE) AS TotalSamples
, (SELECT Count(A.Values)
FROM SAMPLE AS A
WHERE A.Values <= SAMPLE.Values)/(SELECT Count(Values)
FROM SAMPLE) AS Percentage
, -Int(-20*((SELECT Count(A.Values)
FROM SAMPLE AS A
WHERE A.Values <= SAMPLE.Values)/(SELECT Count(Values)
FROM SAMPLE)))*20 AS Percentile
FROM SAMPLE
ORDER BY SAMPLE.Values

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..