Getting a query to calculate a percentile... I think I'm reallllly close...

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;
 
M

[MVP] S.Clark

First, the blatant plug:

Total Access Statistics:
http://www.fmsinc.com/products/statistics/index.html

Next, if you choose to roll your own, instead, then I would recommend
breaking your current statement into at least two queries. It may run
slower, but your current one is impossible to debug.

Perhaps you could break it into some action queries to write the data to
some temp tables to see the progression of the data.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Not so average percentile question... 2
SUM in a UNION query 2
Using a fields drop down selection to populate 2nd fields drop dow 1
This one's a challenge: 1.9 not equaling 1.9..... What's going on??? 5
Building a query to calculate percentages 1
Getting a count query to return zero. 4
LINQ to SQL query, getting a bit lost. 2
HowTo Use a form to query results back to another field in form. 1

Top