Not so average percentile question...

[pwizzle]

Hi all,

The answer to this question is probably very simple, but I feel like
I'm looking right over it...

I have a query with 4 fields:

Vendor_Name
Number_of_Items
Number_Correct
Ratio (Which is: Number_Correct/Number_of_Items)

I want to add a 5th field into this query that calculates the
percentile rating of each vendor based on their ratio.

In other words, if I'm a vendor in this database with 200 other vendors
and 98 out of 100 of my items are correct (ratio of .98) then
(depending on how good the other vendors are) I would like to know that
I'm in the 95th percentile (or whatever).

Calculating a percentile is easy... it's just the number of vendors in
the query that have a ratio <= the current ratio... all divided by the
total number of points.

How do I do this? I tried making use of the module found here:
http://www.mvps.org/access/queries/qry0019.htm but it doesn't seem to
fit what i'm looking for.

 

[Gary Walter]

I have a query with 4 fields:

Vendor_Name
Number_of_Items
Number_Correct
Ratio (Which is: Number_Correct/Number_of_Items)

I want to add a 5th field into this query that calculates the
percentile rating of each vendor based on their ratio.

In other words, if I'm a vendor in this database with 200 other vendors
and 98 out of 100 of my items are correct (ratio of .98) then
(depending on how good the other vendors are) I would like to know that
I'm in the 95th percentile (or whatever).

Calculating a percentile is easy... it's just the number of vendors in
the query that have a ratio <= the current ratio... all divided by the
total number of points.

Shouldn't that be "divided by total number of samples?"

If I understand correctly, then here might be one way:
(change "yurquery" to name of your query)

SELECT
q.Vendor_Name,
q.Number_of_Items,
q.Number_Correct,
q.Ratio,
(SELECT
COUNT(*)
FROM
yurquery AS y
WHERE
y.Ratio <= q.Ratio) AS NumLTorEQ,
(SELECT
COUNT(*)
FROM
yurquery AS z) AS NumSample,
NumLTorEQ/NumSample AS Percentile
FROM
yurquery AS q;

 

[pwizzle]

Hi Gary, (or anyone reading)

I think that we're on the right track... but it doesn't appear to be
working. Here's my code:

SELECT [q].[Vendor Name], [q].[Total Audited], [q].[Items in
Tolerance], [Items in Tolerance]/[Total Audited] AS Ratio,

(SELECT COUNT(*)
FROM [Audit Totals (Complete List)] AS y
WHERE ([y].[Items in Tolerance]/[y].[Total Audited]) <= ([q].[Items
in Tolerance]/[q].[Total Audited])) AS NumLTorEQ,

(SELECT COUNT(*)
FROM [Audit Totals (Complete List)] AS z) AS NumSample,
NumLTorEQ/NumSample AS Percentile

FROM [Audit Totals (Complete List)] AS q;