Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rank or Row Number (Access 2000 (9.0.3821 SR-1))

    I have a query with various calculations returning 265 rows sorted by a calculated field.

    The answer (sorted ascending) is actually ranking of rows from 1-265. I need to have a separate column in the query that returns either the ranking in order or simply the row number which will in essence be the same as the rank.

    In a report I can do this with an =1 in a query I have no clue.
    There is always a way.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

    <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=96095&page=& view=&sb=&o=&vc=1#Post96095>This should do what you want, or at least point you in the right direction.
    </A>

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

    Perfection!
    I am sure this is the answer!
    Happy New Year! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>
    There is always a way.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

    I can not believe I am dusting this one off after almost a year but I have a new issue...

    The solution was offered and it was perfect for a long time. Now I am in a new Db and using the EXACT format for the ranking and I am getting the higest number repeated in all rows.

    REMINDER: I have a calculated field and I need to add a sequence number indicating the ranking from highest to lowest.
    Seq: (select count(*) from [1Prod] S where S.[ProdRankScore] >= S.[ProdrankScore] )

    [1Prod] is the table
    [ProdRankScore] is the field to be ranked

    How could this work perfectly in one Db and not in another???

    Any thoughts from the Woody's Lounge Gods will be greatly appreciated!
    There is always a way.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

    I would use :
    Seq: DCount("ProdRankScore","1Prod","[ProdRankScore] >= " & [ProdrankScore])
    Francois

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

    In

    Seq: (Select Count(*) From [1Prod] S Where S.[ProdRankScore] >= S.[ProdRankScore])

    you use S.[ProdRankScore] twice - this makes the condition True for all records, so it will always result in the total number of records. You should replace the first S.[ProdRankScore] by 1Prod.[ProdRankScore]:

    Seq: (Select Count(*) From [1Prod] S Where 1Prod.[ProdRankScore] >= S.[ProdRankScore])

    (Francois' suggestion of using DCount should work too.)

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

    That makes sense... and works perfectly

    Thank you for saving my sanity. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    There is always a way.

Posting Permissions

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