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.
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.
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