Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    214

    Unanswered: Reset Autonum to zero or....

    some other way to have an incrementing number starting with 1. I have a ranking query that I need to show who is in first, second, third, etc...I have been appending the data to a table that has an autonum, but when I delete the data and reappend, it still starts off where it left the time before.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The following query is close to what you want...
    Code:
    SELECT a.runner
         , Count(b.time) As [position]
    FROM   @my_table a
     INNER
      JOIN @my_table b
        ON a.time >= b.time
    GROUP
        BY a.runner
    ORDER
        BY 2 ASC
    The only problem is that if two people get the same time...

    Posting this as inspiration for others to expand on
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mr. blonde
    ...when I delete the data and reappend, it still starts off where it left the time before.
    compact the table after deleting the data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^ That's what I was going to say.
    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
  •