Numer formula in query

[Guest]

I have a query that returns a set of data. I would like to add a field in
that query that automatically calculates a sequential number for each record.

The number should increase incrementally by 1 and start with 1

I am just trying to number the results exactly how they are shown in a
query. Is there a way to do this with a simple calculation?

 

[Allen Browne]

No, there's not a simple way to do that in a query.

Simplest approach is to use a report. Add a text box with these properties:
Control Source =1
Running Sum Over All
More info:
http://allenbrowne.com/casu-10.html

You cannot just call a VBA function with a static variable, since Access
calls the functions in the order the records display on screen, which messes
up if you jump around.

If the query is sorted by a primary key, you can use DCount() or a subquery
to count the number of preceeding records, but you must ensure that the
expression has the same criteria as the query, and it messes up if the user
right-clicks a field in the query results and changes the sorting.

Another alternative is to append the records to a table that has an
Autonumber field.