Dear Sri:
It is possible to "rank" rows within a SELECT query. This assigns
sequential numbers to the rows of a query based on some specified
ordering of those rows, but the numbers will only be sequential if the
ordering is on a column or set of columns that are unique. Otherwise,
the ordering my include "tie" values and the sequencing numbers will
reflect this.
Such a ranking is not "stored" unless you create a column in which to
store it. If you do, this would be an integer data type, but would
not have anything to do with being an "autonumber."
In fact, many rankings of the rows in a query could exist, potentially
one for every combination of columns whether they create a unique
ordering or not. But these rankings are "live" and will typically
change with insertions, deletions, and updates of the values in the
ordering columns.
Consider that, if you did get a table autonumbered into a specific
sequence, that sequencing would generally be destroyed the first time
a new row was inserted (the new row would sequence at the end of the
sort), or a row deleted (not destroying ordering, but leaving a gap in
numbering), or especially if the ordering column(s) were updated to
new values (destroying the correlation between the autonumber ordering
and the ordering by some key which you originally created).
I hope I have treated the subject above in at least outline form, and
that it is relevant to your original question. Please let me know.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
