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])....
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
