P
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
Total Audited
Items In Tolerance
Ratio (Which is: Items In Tolerance/Total Audited)
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 samples.
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.
Here's what I've tried, but it seems to get stuck in loop of some
kind...
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;
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
Total Audited
Items In Tolerance
Ratio (Which is: Items In Tolerance/Total Audited)
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 samples.
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.
Here's what I've tried, but it seems to get stuck in loop of some
kind...
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;