Numerical Sequence

<< Click to Display Table of Contents >>

Navigation:  Queries >

Numerical Sequence

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.

key Your Sample Database Is Called   "countercolumn.accdb"

 

This can be purchased with all the other downloads on this page