Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2008
    Posts
    27

    Unanswered: How to place a ranking order

    I have a similar question before, but was not able to come up with the solution I was hoping for, I am hoping I can word it better this time. I am looking to rank individuals based upon three criteria: State, Compliance and Total.

    For Example
    State Total Compliance Rank
    CA 500 100% A1
    CA 487 100% A2
    CA 500 95% B1
    CA 487 95% B2

    I want to have the the Letter of the rank be based off of the compliance and then within each letter have the 1,2,3....be based upon the higher of the totals for each person. Any help on this would be great. I have been trying everything for over a week and am not getting anywhere.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you have a query which produces the letter part of the rank?

    if so, please show it

    you will need to save this query and then write an additional query using this query in the FROM clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Posts
    27
    Here is what I have for the letter, now I just need to get the number and place the two together:

    Active Grade: IIf([Active Summary - CMS]![Compliance]=1,"A",IIf([Active Summary - CMS]![Compliance]>=0.9,"B",IIf([Active Summary - CMS]![Compliance]>=0.5,"C",IIf([Active Summary - CMS]![Compliance]>=0,"D"))))

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I still don't fully understand what it is you want to do.

    Perhaps you could post up the results you want to see for your sample data?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i uderstand the requirement, but i need to see the entire query

    could you go into SQL View and copy the entire SELECT statement from there and paste it here please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2008
    Posts
    27
    Here is my full SQL:

    SELECT [Atty List].State, [Atty List].Atty, CDbl(Nz([Active Summary - CMS]![On Time],0)) AS [Active Loans on Time], CDbl(Nz([Active Summary - CMS]![Total Count],0)) AS [Total Active Loans], [Active Summary - CMS].[Compliance], [Active Summary - CMS].Month, IIf([Active Summary - CMS]![Compliance]=1,"A",IIf([Active Summary - CMS]![Compliance]>=0.9,"B",IIf([Active Summary - CMS]![Compliance]>=0.5,"C",IIf([Active Summary - CMS]![Compliance]>=0,"D")))) AS [Active Grade], CDbl(Nz([DTS Summary - CMS]![On Time],0)) AS [Days to Sale On Time], CDbl(Nz([DTS Summary - CMS]![Total Count],0)) AS [Days to Sale Total Count], [DTS Summary - CMS].[Compliance], [DTS Summary - CMS].Month, IIf([DTS Summary - CMS]![Compliance]=1,"A",IIf([DTS Summary - CMS]![Compliance]>=0.9,"B",IIf([DTS Summary - CMS]![Compliance]>=0.5,"C",IIf([DTS Summary - CMS]![Compliance]>=0,"D")))) AS [DTS Grade], IIf([Active Summary - CMS]![Total Count] Or [DTS Summary - CMS]![Total Count]>0,"Yes","No") AS [Active with CMS], IIf([Active Summary - CMS]!Month=[DTS Summary - CMS]!Month Or [DTS Summary - CMS]!Month Is Null Or [Active Summary - CMS]!Month Is Null,"Yes","No") AS [Dup Formula] INTO [CMS Master]
    FROM ([Atty List] LEFT JOIN [Active Summary - CMS] ON ([Atty List].Atty = [Active Summary - CMS].[Attorney Name]) AND ([Atty List].State = [Active Summary - CMS].State)) LEFT JOIN [DTS Summary - CMS] ON ([Atty List].Atty = [DTS Summary - CMS].[Attorney Name]) AND ([Atty List].State = [DTS Summary - CMS].State)
    WHERE (((IIf([Active Summary - CMS]![Total Count] Or [DTS Summary - CMS]![Total Count]>0,"Yes","No"))="Yes") AND ((IIf([Active Summary - CMS]![Month]=[DTS Summary - CMS]![Month] Or [DTS Summary - CMS]![Month] Is Null Or [Active Summary - CMS]![Month] Is Null,"Yes","No"))="Yes"))
    ORDER BY [Atty List].State, [Atty List].Atty;

    As you see I used the Grade query twice, due to the fact that I am grading, DTS files and Active files.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, remove the INTO clause, the WHERE clause, and the ORDER BY clause, add four new column aliases (shown in red), and save this query, let's call it Atty_query_1
    Code:
    SELECT [Atty List].State
         , [Atty List].Atty
         , CDbl(Nz([Active Summary - CMS]![On Time],0)) AS [Active Loans on Time]
         , CDbl(Nz([Active Summary - CMS]![Total Count],0)) AS [Total Active Loans]
         , [Active Summary - CMS].[Compliance] AS Active_Compliance
         , [Active Summary - CMS].Month AS Active_Month
         , IIf([Active Summary - CMS]![Compliance]=1,'A',
           IIf([Active Summary - CMS]![Compliance]>=0.9,'B',
           IIf([Active Summary - CMS]![Compliance]>=0.5,'C',
           IIf([Active Summary - CMS]![Compliance]>=0,'D')))) AS [Active Grade]
         , CDbl(Nz([DTS Summary - CMS]![On Time],0)) AS [Days to Sale On Time]
         , CDbl(Nz([DTS Summary - CMS]![Total Count],0)) AS [Days to Sale Total Count]
         , [DTS Summary - CMS].[Compliance] AS DTS_Summary_Compliance
         , [DTS Summary - CMS].Month AS DTS_Summary_Month
         , IIf([DTS Summary - CMS]![Compliance]=1,'A',
           IIf([DTS Summary - CMS]![Compliance]>=0.9,'B',
           IIf([DTS Summary - CMS]![Compliance]>=0.5,'C',
           IIf([DTS Summary - CMS]![Compliance]>=0,'D')))) AS [DTS Grade]
         , IIf([Active Summary - CMS]![Total Count] 
            Or [DTS Summary - CMS]![Total Count]>0,'Yes','No') AS [Active with CMS]
         , IIf([Active Summary - CMS]!Month=[DTS Summary - CMS]!Month 
            Or [DTS Summary - CMS]!Month Is Null 
            Or [Active Summary - CMS]!Month Is Null,'Yes','No') AS [Dup Formula] 
      FROM (
           [Atty List] 
    LEFT 
      JOIN [Active Summary - CMS] 
        ON [Atty List].Atty = [Active Summary - CMS].[Attorney Name] 
       AND [Atty List].State = [Active Summary - CMS].State
           ) 
    LEFT 
      JOIN [DTS Summary - CMS] 
        ON [Atty List].Atty = [DTS Summary - CMS].[Attorney Name]
       AND [Atty List].State = [DTS Summary - CMS].State
    important note: your IIF formula for [Active with CMS] and [Dup Formula] look like they are missing an operator before the OR

    now, run the following query and confirm it is producing the correct results
    Code:
    SELECT State
         , Atty
         , [Active Loans on Time]
         , [Total Active Loans]
         , Active_Compliance
         , Active_Month
         , [Active Grade]
         , [Days to Sale On Time]
         , [Days to Sale Total Count]
         , DTS_Summary_Compliance
         , DTS_Summary_Month
         , [DTS Grade]
         , [Active with CMS]
         , [Dup Formula] 
      FROM Atty_Query_1
     WHERE [Active with CMS]='Yes'
       AND [Dup Formula]='Yes'
    ORDER 
        BY State
         , Atty
    let me know if everything's okay with this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2008
    Posts
    27
    So far so good. What SQL do we need next to be able to produce the numeric side of the grade now?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by afors112
    So far so good. What SQL do we need next to be able to produce the numeric side of the grade now?
    surely should read...
    So far so good. What SQL do I need next to be able to produce the numeric side of the grade now?

    A suggestion: give it a whirl yourself, and come back if or when you get stuck, but at least please dip you toe into the water to find a solution first. that way round you have a better chance of learning from the problem, or at least fault finding in SQL rather than getting a solution that you don't understand or can't apply to other problems


    just my tuppeny ha'porths worth

  10. #10
    Join Date
    Feb 2008
    Posts
    27
    I would not be on here if I have not tried this out many times and I have tip toed in getting an answer myself.

    It is people like you who make novice analysts not want to ask questions, and I thought a forum was about asking questions...

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, the ranking is the next stage

    what column is it based on? what determines the ranking? in your first post it's simply called 'Total'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by afors112
    I would not be on here if I have not tried this out many times and I have tip toed in getting an answer myself.

    It is people like you who make novice analysts not want to ask questions, and I thought a forum was about asking questions...
    Hi afors, you must give us frequentors a break too; far too often do we have people who try to play us.

    ...even more so now student deadlines are looming!

    Good for you for having a go, keep it up
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2008
    Posts
    27
    The Rating would be based upon: Each state and within each state based upon the highest total of active loans. I want these within each month as well, so that I can choose any month and get a rank or choose all months and get a rank. Below is what I would like my final result to be:

    State Atty Compliance Total Active Loans Month Rank
    CA 1 100% 500 1/1/08 A1
    CA 2 100% 400 1/1/08 A2
    CA 3 100% 300 1/1/08 A3
    CA 4 90% 500 1/1/08 B1
    CA 5 90% 400 1/1/08 B2
    NV 1 100% 500 1/1/08 A1
    NV 2 100% 400 1/1/08 A2
    NV 3 100% 300 1/1/08 A3
    NV 4 90% 500 1/1/08 B1
    NV 5 90% 400 1/1/08 B2

    Hope this example helps. I really do appreciate you helping me!!

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see if this gives you the rank you want --
    Code:
    SELECT State
         , Atty
         , [Active Loans on Time]
         , [Total Active Loans]
         , Active_Compliance
         , Active_Month
         , [Active Grade]
         , ( select count(*) + 1
               from Atty_Query_1
              where State = T.State
                and [Total Active Loans] > T.[Total Active Loans]
           ) as rank  
         , [Days to Sale On Time]
         , [Days to Sale Total Count]
         , DTS_Summary_Compliance
         , DTS_Summary_Month
         , [DTS Grade]
         , [Active with CMS]
         , [Dup Formula] 
      FROM Atty_Query_1 AS T 
     WHERE [Active with CMS]='Yes'
       AND [Dup Formula]='Yes'
    ORDER 
        BY State
         , Atty
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2008
    Posts
    27
    Looks really close, I think I can use what you have given me to fine tune it. Again I really appreciate your help!

Posting Permissions

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