Results 1 to 7 of 7

Thread: Ranking Data

  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Ranking Data

    I have searched multiple places for this information and find nothing which can help me achieve my goal.

    I have a query which pulls data from numerous tables and performs calculations on certain fields.
    The resulting query shows 4 fields with calculated data.
    I can sort these fields ascending or descending.
    If I sort field 2 descending, then field 1 is random.
    I would like to add a ranking number to each field so that I can easily sort any field, yet have a ranking number for the 3 other fields.
    In other words I wish to display 8 fields - Field 1 Data, Field 1 Ranking, Field 2 Data, Field 2 Ranking, etc.

    Example of what I wish to see:
    I sort field 2 descending (meaning the high value = rank 1) and I can see that for this record it ranks 5th in field 1, 3rd in field 3 and 10th in field 4.

    Is this possible and how would I go about achieving such?
    Last edited by Montague; 06-17-09 at 23:40.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Go-go SQL gurus !!

    I'd have to run multiple queries, generating a new table with the needed data to accomplish that.
    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

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    You see, I have tried that, but I run into problems.

    The example I mentioned lists 4 columns of data, but that is a sample. If I estimate roughly I will have at least 8 columns such as this. Perhaps more.

    One solution would be to fill a table for each column using an update table query, which would have an autonumber field. This can work and is dandy, but the problem comes that the data needs to be refreshed regularly (at least daily) and thus the records need to be deleted in each of these tables and in order for the autonumber to reset to zero, the database needs to be compacted. This equals a lot of work getting all the tables empty and refreshed and a macro cannot compact the database unless it is run from a second database.

    I cannot find a solution which will allow me to use the Make Table query, since I do not know how to add a new column to the table which will contain an autonumber field.

    Aye, I am stoopid.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No, autonumbers aren't the way to go... I was thinking about recordset processing. How many RECORDS are we talking about?

    I still think the SQL gurus around here might have something more efficient -- I see them talking about numbers tables to do stuff like rankings, so I'd wait to see if any of them reply before doing much.
    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
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    And you are not stoopid! This particular need is not exactly "easy"!!
    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

  6. #6
    Join Date
    Jun 2009
    Posts
    3
    At present I only have 34 records. I estimate that this will never go beyond 100 records at maximum.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Hello? SQL Guru needed here!
    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

Posting Permissions

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