Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2014
    Posts
    2

    Unanswered: Making a new Col to RANK values from 2 other Cols

    Hi Folks

    I am a relative novice with Access 2013 but can do basic Queries, Relationships etc

    At the moment I have a Query (Query2) with 2 Cols:

    "CourseID"
    "Final Rating"

    CourseID may have 10 rows with the same value eg "201250"
    Final Rating will have a value for each of those 10 CourseID rows

    eg

    201250 343
    201250 456
    201250 180 etc

    Now what I want to do in a separate Col is to assign a Rank from 1 to 10 (for this example) to each of those Final Rating values for the same CourseID grouping, so here 456 will return 1, 343 will return 2 and 180 will return 3 etc

    Now my 3 Cols would look like this:

    201250 343 2
    201250 456 1
    201250 180 3 etc

    I would not want it to Rank from 2 where there may be equal values in Final Rating for a group in CourseID but to assign 1 to the equal highest.

    I would need the complete dummies guide on how to do this if anyone can assist.

    Thanks
    Hammer

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    I do this by, using an append query with TOP 50 sort decend, to add to my 'report' table.
    If you want , you can take the results from this table and update your master table.
    (remember , your 'report' table will have autonumber to produce the ranking,...hence the sort decend)

  3. #3
    Join Date
    Nov 2014
    Posts
    2
    Many thanks!
    I will give this a try.

    Cheers
    Hammer

Posting Permissions

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