Numerical Sequence

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

About Garry Robinson

He is the guy who originally put this Smart Access web site together. Head to www.gr-fx.com.au for more about him and his company. His main claim to fame is winning the Microsoft MVP award for services to MS Access from 2006 to 2018.
This entry was posted in Queries. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.