How do I rank data into deciles in Access?

[Gonville]

Hi,
I'm looking for a way to take a list of performance data and rank it into
deciles (i.e. 1 for all members of the highest decile and 10 for the lowest
group). In essence, therefore, I want to take a table with two columns
("Name" and "% Return") and add a third ("Decile").
The length of the data will vary - sometimes 10-20 rows, up to a few
hundred. It's easy to do in Excel - it's just the PERCENTRANK function
multiplied by 10 and rounded to the nearest integer, but I need to do the
same in Access (2003 version).
If anyone has any ideas, I would be most grateful.
Thanks,
Gonville

 

[Marshall Barton]

I'm looking for a way to take a list of performance data and rank it into
deciles (i.e. 1 for all members of the highest decile and 10 for the lowest
group). In essence, therefore, I want to take a table with two columns
("Name" and "% Return") and add a third ("Decile").
The length of the data will vary - sometimes 10-20 rows, up to a few
hundred. It's easy to do in Excel - it's just the PERCENTRANK function
multiplied by 10 and rounded to the nearest integer, but I need to do the
same in Access (2003 version).

Nothing built in to do that, but it can be calculated using
nested subqueries. Here's some air code with the general
idea:

SELECT Name, [%Return],
(10 * (SELECT Count(*)
FROM table As X
WHERE X.[%Return] <= T.[%Return]
)) \ (SELECT Count(*) FROM table) As Decile
FROM table As T

 

[LeAnne]

Hi Gonville,

There's no native Access function that will accomplish this. There's a
way to call Excel functions from Access; read the details at

http://support.microsoft.com/kb/198571/EN-US/

I use a nifty Access add-in utility called Total Access Statistics
produced by FMS, Inc. Among other things, it can parse data into
centiles (1nth thru 99nth), deciles, and quartiles. FMS has a free trial
version with a 30-day use period (works on tables <100 records only, no
queries) available for download on its website. Check it out at

http://www.fmsinc.com/Products/statistics/index.html

PS. The full-blown version is much more versatile. I use it constantly.
The single-user license is reasonably priced, considering how much you'd
pay for SAS or S+, & how much time you'd spend importing & exporting
data back and forth.

hth,

LeAnne