Creating a Field With Percentile Group

  • Thread starter Thread starter KevinC
  • Start date Start date
K

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
 
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----------
 
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
..
 
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
'====================================================
 
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.
 
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
..
 
Back
Top