Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: Adding Row Or Rank Number In Access (w/o VB)

    A coworker recently showed me a way to add a row or rank number to a table in Access without Visual Basic. For those who know Access but haven't yet delved into VB, I thought this might be helpful:

    1. Create a maketable query with the fields you want. Set up the sort order in the query so records are in the order you want them ranked. Save and run the query.

    2. Go to the created table in design view. Create a new field "#" and change the datatype to autonumber. Go to the datasheet view and you will then see the autonumber field numbering from 1,2,3,4,... Save table.

    3. Go back to the maketable query you made in step 1 and change it to an append query, appending the table you just created. Save the query.

    At this point when you run the append query, it will add to the table and it will assume the next rank number available (note, if you delete all records from the table and then run the append query, it will not start the rank at 1 again unless you compact and repair the database (not recommended) or do the following steps ).

    5. Create a select query that has only the "#" field from the table and 'total' it by minimum (Min). Save the query.

    6. Create a select query and pull the table and the minimum query. Select all fields but the "#" field from the table and build a function to subtract the minimum number from the number in the table and then add 1. This will give you a corrected rank/row number.

    It may seem convoluted but it works. I'd probably setup a macro to run a delete query to clear all records from the table, run the append query and then run the query in step 6. Could probably also change the query in step 6 to an update query to update the table.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good luck with that.

    Try deleting a record (and/or start typing in a new record, then press ESCAPE if # is an AutoNumber) and the entire process will have to be repeated.

    Much better to use VBA... or not use row numbers.
    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
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    This is assuming record changes are done to the original table pulled into the query in step 1 or that you are limiting results by your criteria. If this is done, the process will still yield correct row/rank numbers. I agree it is not so useful if you plan on performing maintenance to the resulting table, it would be better to use code if you were.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Windows Functions (the technical name for this and other related functions) are part of the ISO standard and were introduced to SQL Server 2005. They are available in Oracle and other RDBMSs. They are utterly ******* brilliant and very, very useful. I fear it will be a cold day in hell before Access gets them though
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    That and a report output format end recipients can actually open. Thank goodness for Leban's PDF converter. JB

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by scrtchmstj
    That and a report output format end recipients can actually open. Thank goodness for Leban's PDF converter. JB
    You mean snapshot reports? You know that internet explorer will open these, not just report viewer?

    But yes - there are many things Access is awesome at, but there is another long list of things I would love it to have (or lose).
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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