How do I return a Percentile Rank in Microsoft Access?

[bobmount]

I'm trying to add a field to a "Make Table" query in Access which will add a
column representing the percentile rank of the [Value] field, based on the
other values in that column (i.e., 1%-100%)....similar to the PERCENTILE RANK
feature in Excel. Does anyone know how to do this? (Assume my table name
is 'DataSet', and the field with the values to rank is [Value])....

Thanks, in advance!

[Gary Walter]

Hi Bob,

Are we talking about:

Percentile = (Rank/TotalCount) * 100

The following solution requires that you
be able to use DCount to produce a rank
and a total count over the group.

SELECT
D.[Value],
DCount("*","DataSet", "[Value]<" & D.[Value] ) AS ValRank,
DCount("*","DataSet") AS TotCnt,
([ValRank]/[TotCnt])*100 AS Percentile
FROM DataSet AS D;

the above won't resolve ties though (but can with
some other field added to DCount for ValRank...

good luck,

gary

[bobmount]

Thanks, Gary....It seems like your query WOULD achieve what I'm after, but I
can't seem to get the syntax right for use within an existing "Make Table"
query in Access.

Basically, I'd like to simply add the percentile_rank fields (as, I think,
you've defined it) to the Access Query that's currently defined by the
following SQL:

SELECT Category, Name, Value INTO Product_1
FROM DataSet
WHERE (((Category)="Product_1"));

Would you mind integrating your SQL into this structure? I've tried
fiddling with it for hours, and can't seem to get it right....

Thank you sooooo much (in advance!)....

B

[Gary Walter]

Hi bob,

Unless this is for homework....

{meaning no offense}

please stop what you are doing and
go out and get a good book on database
and table design...honestly.

Do you anticipate "making" tables Product_2,
Product_3, Product_4, etc.? For what?

...or maybe I just don't understand...

In the calc for

Percentile = (Rank/TotCnt) * 100

is TotCnt over entire DataSet,

or over the filtered DataSet?

If "over filtered DataSet"....

Method 1) Divide and conquer
(recommend)

qryPreFilter:

SELECT Category, [Name], [Value]

FROM DataSet
WHERE (((Category)="Product_1"));

SELECT
Q.Category,
Q.[Name],
Q.[Value],
DCount("*","qryPreFilter", "[Value]<" & Q.[Value] ) AS ValRank,
DCount("*","qryPreFilter") AS TotCnt,

([ValRank]/[TotCnt])*100 AS Percentile

INTO Product_1
FROM qryPreFilter AS Q;

Method 2) Add filter to (one or both?) DCount

SELECT
D.Category,
D.[Name],
D.[Value],
DCount("*","DataSet", "[Value]<" & D.[Value],
"[Category] = 'Product_1'" ) AS ValRank,
DCount("*","DataSet","[Category]='Product_1'") AS TotCnt,

([ValRank]/[TotCnt])*100 AS Percentile

INTO Product_1
FROM DataSet AS D
WHERE D.Category = "Product_1";

Method 3) Update after make table

SELECT
D.Category,
D.[Name],
D.[Value],
IIf(True,Null,CDbl(0)) AS Percentile
INTO Product_1
FROM DataSet AS D
WHERE D.Category = "Product_1";

UPDATE Product_1 AS P SET
P.Percentile =
( DCount("*","Product_1", "[Value]<" & P.[Value]) /
DCount("*","Product_1") ) * 100;

good luck (with your homework?),

gary