Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Unanswered: Show a Ranking in a Report

    All,
    I looked thru the help files and scanned around but didn't come up with an answer.

    I am creating a report that will show different buildings and the audit scores that they got. In Excel, we used the RANK function to show who was number 1, who was number 2, etc.

    I can't find a way to do the same thing in Access, any ideas?

    RANK isn't listed as a function in the Access Functions list, but when you type it in the expression builder and add a parenthesis, it does see it as a function but I won't have a cell range to reference it to.

    Any and all help appreciated!

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

  3. #3
    Join Date
    Dec 2005
    Posts
    121
    Thanks Pappa Smurf! I'll give it a try!

  4. #4
    Join Date
    Dec 2005
    Posts
    121
    I'm new to using the SQL feature on Access Queries, so....

    My table name is DC_Scorecard. The is a field that is named Audit, which is grouped and will have 10 entries under the DC field (different DC numbers). The ranking criteria will come from a field named Accuracy_Percent.

    I have tried a couple of combinations using your example and changing out the names, but it doesn't like it. Any suggestions?

  5. #5
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Please post a zipped copy of your table with some data and also the queries used for your grouping etc.

  6. #6
    Join Date
    Dec 2005
    Posts
    121
    Here is the table and the query.
    Attached Files Attached Files

  7. #7
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Bob

    Would please zip and send the table as it is linked to a back end and does appear in your attachment.

  8. #8
    Join Date
    Dec 2005
    Posts
    121
    Man, what a goof, I knew that, don't know why I imported the link!!!

    Sorry about that, here you go!
    Attached Files Attached Files

  9. #9
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Bob

    Attached is the updated version with the ranking.

    1. I created a new query using your query DC_Scorecard_Report
    2. In the new query I changed the alias of the query to Final_Rankings
    3. I then changed the code in the Rankings column to the relevant fields of the query
    4. I saved the query as qry_rankings.

    Note This query does not show the next number if there are more than one on the same ranking. In your example there are 27 records with a ranking of 1 the next record shows a ranking of 28 and not 2.
    Attached Files Attached Files

  10. #10
    Join Date
    Dec 2005
    Posts
    121

    Smile

    Thank you sir, I knew there had to be a way to show that information.

    Thanks for your help!

  11. #11
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    It was a pleasure doing your request.

    Go to this knowledge based site http://support.microsft.com/kb/120608/en-us it describes How to Rank records Within a Query.

  12. #12
    Join Date
    Feb 2009
    Posts
    5
    Quote Originally Posted by Poppa Smurf
    Bob

    Attached is the updated version with the ranking.

    1. I created a new query using your query DC_Scorecard_Report
    2. In the new query I changed the alias of the query to Final_Rankings
    3. I then changed the code in the Rankings column to the relevant fields of the query
    4. I saved the query as qry_rankings.

    Note This query does not show the next number if there are more than one on the same ranking. In your example there are 27 records with a ranking of 1 the next record shows a ranking of 28 and not 2.

    Hi to all!

    I would like to know how should I modify the SQL code from the query in order to add a level in the ranking.
    For example, In the "ranking.mdb" database, how would I get the ranking based on the "accuracy percent" within each "Audit" group (first field)?

    The final result would be something like :

    Audit ....blabla... Accuracy Percent Rank
    ACA ............... 100% 1
    ACA ............... 99% 2
    ACA ............... 50% 3
    Fashion ............... 100% 1
    Fashion ............... 85% 2
    Fashion ............... 40% 3

    etc....

    i m kinda stuck I m sure there is a "group by" to add somewhere but can t find where...
    Thanks a lot in advance....
    Last edited by sandra42; 02-19-09 at 16:05.

  13. #13
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You can use a union query and queries for each level to do the grouping. The only problem will be when you have a new item to group or you remove an item then you will need to make changes to the union query and other relevant queries.

  14. #14
    Join Date
    Feb 2009
    Posts
    5
    hi Poppa Smurf! thanks for your input!

    So if I understand correctly you're suggesting that I create as many queries as I have groups to get for each group the rankings and then I use one Union query to put everything in only one table?

    I think I could do this...I was hoping there would be something more elegant...
    I also have 66 groups .So that would make at least 66 more queries in mydb...
    I could also export the result to excel but I need to stick to access :-(

    thanks for your help,best

    Sandra

  15. #15
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Sandra

    Attach is a sample database that will suit your requirements form frm_demo should load when you open the database. I used some code instead of queries to produce the same result.

    Using your layout see below, it will show the final result similar to your requirement except I used a different field in the table. The ranking will restart at the start of each change of name in the audit field and if there are equal scores the same placing will be displayed until there is a change in score.

    When I have time I will look at placing an = to show scores that are equal e.g. =2, =2 etc.

    The final result would be something like :

    Audit ....blabla... Accuracy Percent Rank
    ACA ............... 100% 1
    ACA ............... 99% 2
    ACA ............... 50% 3
    Fashion ............... 100% 1
    Fashion ............... 85% 2
    Fashion ............... 40% 3
    Would you please post to the forum or send to my email address a copy of your data so that I can test the code? Please remove any confidential data.
    Attached Files Attached Files

Posting Permissions

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