How do I return a Percentile Rank in Microsoft Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
bobmount" said:
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])....
Click to expand...
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
 
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 said:
bobmount" said:
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])....
Click to expand...
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
Click to expand...
 
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

bobmount said:
....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 said:
bobmount" said:
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])....
Click to expand...
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
Click to expand...
Click to expand...
 
Back
Top