How to create auto number column in a query based on all current query records (1 Viewer)

[MicroAbdel]

Hi access dev & fans
As u guys mention in Title i want to create a field 'column' not related with table, that column shows me auto-numbers from first record to last record ; auto number simple like "1 to 1+" or "01 to 01+"
if i can do that without any VBA code will be great cuz i'm not familiar with :/
THANKS FOR HELP!<3

 

[arnelgp]

It depends entirely on the Sorting of your table.
you can use "that" field to DCount()/or Count the
records "less than/less than or equal" to "that" field.

if you are sorting on autonumber:

select DCount("1","theTable","theField <= " & [theField]) As [Auto-Number], * From theTable Order By theField;

 

[Pat Hartman]

A query that joins the table to itself using a non-equi-join is far more efficient than dCount(). However, both solutions require a unique identifier that is in the sequence you want the generated number. So if you have an existing autonumber and the records you want have been entered into the table in sequence so that the autonumbers are ascending but not necessarily consecutive, you can use the autonumber to generate the sequence. Or if you have a date field that includes time, that will work as long as there are no duplicates.