vb123.com

Garry Robinson's Popular MS Access, Office and VB Resource Site

Home Contact Us
Order our Software

RSS & Newsletter
Join our XML/RSS Newsfeed or sign up for our informative newsletter on Office Automation, Access and VB topics
Read More

Get Good Help
If you need help with a database, our Professionals could be the answer
Read More

Is Your Database Corrupt ?
If you have a corrupt database, Try our Access Recovery service

The Workbench Find out who has your database open, start the correct version of Access, easy compacting and backups, change startup options, mde compile, shutdown database Read and Download

The Toolshed
Searchable help file comprising of all the information at vb123.com plus hidden downloads etc. Read More

The Toolbox
Libraries of software that we regularly import into our projects. Enhances the Toolshed More..

Upsize to SQL
Upsize to SQL Server 2005 or 2008 or improve the performance of your Access to SQL Server application
Read More

DryToast New
Backup and query your BaseCamp® projects
Read More

Datamining/Graphs
Explore your data with this versatile graphing and data mining shareware tool. Read More

Garry's Blog
Find out a few other things that Garry has been writing about Microsoft Access. Read more

About The Editor 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
Contact Us ...

Search ...

or try our new Expression Web
vb123.com.au

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.

Downloads
Click here for the download file if you own "The Toolshed" Else click here

Note: Look at qryNumericalSequence then qryAppendCounters in download database

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

Consolidation Queries
Tricky Queries To Impress Your Boss
Lookup Tables - Getting Rid Of Junk data

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