Results 1 to 2 of 2
  1. #1
    sl0140 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    1

    Access rankingTop 3 per group

    Hi guys,



    im trying to get a querie that will give me the top 3 scores based on section and category,
    I have the below querie that gives me the results, but when i specify a different category and sectionm the ranking number is based on the whole record set.

    Code:
    SELECT qfinal1.T_Category.DESC AS CategoryDesc, qfinal1.T_SECTION.DESC AS SectionDesc, qfinal1.EXHIBIT_ID, qfinal1.[Total Score], (select count (*) from q_judges_final_Scores where [total score] > [qfinal1].[total score])+1 AS ranking, qfinal1.SECTION, qfinal1.CATEGORY, qfinal1.LABEL_MEMO
    FROM Q_Judges_Final_Scores AS qfinal1
    GROUP BY qfinal1.T_Category.DESC, qfinal1.T_SECTION.DESC, qfinal1.EXHIBIT_ID, qfinal1.[Total Score], qfinal1.SECTION, qfinal1.CATEGORY, qfinal1.LABEL_MEMO
    HAVING (((qfinal1.[Total Score])<>0))
    ORDER BY qfinal1.[Total Score] DESC;
    I want the rank number to reset for the top 3 of each section/category..

    im close with this , i know i can add TOP 3 after the select, but i dont know how to get the rank number to be based on the same filter...

    Anyw help would be great!

    Thanks

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 02-22-2013, 04:57 PM
  2. Replies: 5
    Last Post: 09-22-2011, 02:10 PM
  3. Replies: 5
    Last Post: 11-29-2010, 08:16 AM
  4. Starting an Access database for a group...
    By kkrishna in forum Access
    Replies: 8
    Last Post: 05-19-2010, 05:29 PM
  5. columns for group detail but not group header?
    By Coolpapabell in forum Reports
    Replies: 0
    Last Post: 08-21-2009, 08:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums