MS Access Running Counter In Query

This video is part of a solution to an interesting question that
I know most developers have faced or are facing… “How can I
number my rows in an Access query?”

tblRunningSum:
Lists numbers starting with 8, and shows
how qryIncrement can start at 1 and increment
this count from 1 to 10.

tblNumbers:
Gives me a list of numbers that I will
append to tblInsertInto.

First I am deleting the contents of tblRunningSum,
and adding the contents of tblNumbers.

Since tblInsertInto has an “id” field of an “autonumber”
the counting would restart at “18”.

By running the query “qryAPPEND”, the counting
will restart at 1.

Here are the SQL statements:

counter: DCount("*","tblNumbers","id<=" & [id])

INSERT INTO tblInsertInto ( ID, Field1 )
SELECT DCount("*","tblNumbers","id<=" & [id]) AS [counter], tblNumbers.letter
FROM tblNumbers;



SELECT tblRunningSum.number, DCount("*","tblRunningSum","number<=" & [number]) AS [counter]
FROM tblRunningSum;

MS Access Running Counter In Query

Click here for the database and the code:
ms-access-running-counter-in-query.accdb


Comments are closed.