Next Tip   How To Make A Numerical Sequence in An Access Query

By Garry Robinson

Creating a numerical sequence for an Access query then appending that to a table (i.e. 1..2..3..4..5..6) in its own column is not as simple as it first seems.

Start with a table like this and the requirement is to compute a Counter field as shown in TestTableOutput.

TestTable
id Num1 date1
5197403 918401 27/02/2007 9:32:39 AM
5216167 918401 28/02/2007 9:21:16 AM
5216358 918401 28/02/2007 9:52:09 AM
5231639 918401 1/03/2007 3:36:06 AM
5249411 918401 2/03/2007 4:00:09 AM
5250208 927222 2/03/2007 6:50:00 AM
5250267 927222 2/03/2007 7:10:48 AM

 

TestTableOutput
Counter afield bfield
1 918401 27/02/2007 9:32:39 AM
2 918401 28/02/2007 9:21:16 AM
3 918401 28/02/2007 9:52:09 AM
4 918401 1/03/2007 3:36:06 AM
5 918401 2/03/2007 4:00:09 AM
6 927222 2/03/2007 6:50:00 AM
7 927222 2/03/2007 7:10:48 AM
8 918401 4/03/2007 4:16:56 AM

TestTableOutput shows the computed column called Counter

Here is the answer,

Id is the autonumber/primary field, add this to a blank column in a new query

DCount("id","TestTable","id <= " & [id]))

Add the ID field as another column and place the only sort in the query on this column. Add any other columns that you want to see as in this query.

SELECT DCount("id","TestTable","id <= " & [id]) AS Counter, Num1, 
date1, TestTable.id
FROM TestTable 
ORDER BY TestTable.id;

Thats it.
 

Notes:  If you use a filter in a query (instead of testTable), the exact same filter must be in the domain aggregate dcount equation.

  Your Sample Database Is Called   "countercolumn.zip"

Sample database is suited to all versions of Access, available in Access 2007 and 2000 formats

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

 
Note: Look at qryNumericalSequence then qryAppendCounters in download database
 

Other Pages at VB123.com That You May Want To Visit

SharePoint and Access - Getting Started and Motivated

Consolidation Queries
Tricky Queries To Impress Your Boss
Lookup Tables - Getting Rid Of Junk data
How To Create A Crosstab Query

Click on the Next Tip button for the next help page in this Access Loop.

 

Our Tools and Resources

  • RSS & Newsletter Here
    Join our newsfeed or sign up for our informative newsletter on Office Automation, Access and VB topics
  • Get Good Help
    If you need help with a database, our Australian Professionals could be the answer

  • Smart Access is online 
    The best magazine written about Microsoft Access is now being transferred to the web. There are 400 articles written by a 100 authors in the collection. 

    Purchase Smart Access

  • The Workbench
    Find out who has your database open, start the correct version of Access, easy compacting and backups, change startup options, creation versions,  shutdown database

  • Convert Access to SQL Server  
    Upsize to SQL Server 2005 or 2008, easily repeated conversions, highly accurate SQL query  translation and web form conversion.
  • Purchase the Popular FMS Products  
    If you purchase the Popular FMS products from us, you will receive a complimentary of Smart Access Gold, Silver or Bronze Collections [Your choice]

 

 

vb123 Professionals


Get Good Help Here

If you need help with a database or Office programming, our Professionals could be the answer because we have worked on many similar solutions


Frontpage Conversions
We have converted vb123.com to Expression Web, contact us if we can help you move to the latest Microsoft web tool.


About The Editor ~ Contact Us
Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia

Access 2003 Security

MS Access Security

Read More here