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.
|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|
|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;
Notes: If you use a filter in a query (instead of testTable), the exact same filter must be in the domain aggregate dcount equation.
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
Our Tools and Resources
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
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