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
