Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2017
    Posts
    1

    Unanswered: Help Needed on Rank Percentile Calculation

    I am a beginner of SQL and would be appreciate a help from anyone who can. I have created ms access query
    SELECT R1.ID, R1.SampleID, R1.HARD, Count(*) AS Rank
    FROM Rep_Stats AS R1, Rep_Stats AS R2
    GROUP BY R1.ID, R1.SampleID, R1.HARD
    ORDER BY R1.HARD;

    which generates the following table;

    ID SampleID HARD Rank
    1995 EN0038966 0.11 1
    1751 EN0040765 0.14 2
    1957 EN0038189 0.14 3
    1732 EN0035089 0.17 4
    1888 EN0039363 0.19 5
    2020 EN0031584 0.21 6


    Now, I want to add a 5th column to the query where each rank value will be divided by the highest rank value (i.e. 6) multiplied by 100. I do not want to create the 5th column as Rank/6, because as the data increases, the highest rank will be greater than 6. I would be glad if anyone could help me use with query to extract the highest rank value (say Rh) and use it to divide all the rank values. Thus, the final query table should look like this;

    ID SampleID HARD Rank PCTL
    1995 EN0038966 0.11 1 (1/Rh)*100
    1751 EN0040765 0.14 2 (2/Rh)*100
    1957 EN0038189 0.14 3 (3/Rh)*100
    1732 EN0035089 0.17 4 (4/Rh)*100
    1888 EN0039363 0.19 5 (5/Rh)*100
    2020 EN0031584 0.21 6 (6/Rh)*100


    NOTE: Rh = Highest Rank Value


    Thank you in advance for your support.
    Last edited by rokine; 06-14-17 at 13:16.

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    188
    Try the Query with the following change of SQL:
    Code:
    SELECT R1.ID, R1.SampleID, R1.HARD, Count(*) AS Rank, Rank/Dmax("Rank","QueryName")*100 AS PCTL
    FROM Rep_Stats AS R1, Rep_Stats AS R2
    GROUP BY R1.ID, R1.SampleID, R1.HARD
    ORDER BY R1.HARD;
    Replace the 'QueryName' with the correct name of the Query.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •