Sequential Numbers in a Query

[Harv Lake]

Hi, maybe you can help me out.

I would like to be able to query a table and have the
results returned with a sequential number as a field.

Say I have a table with the fields first_name and
last_name. I'd like to be able to sort either way and
get something like below

Seq last_name first_name
1 Anderson Joe
2 Bailey Adam
3 Jones Bill

Any suggestions would be appreciated.

Thanks
Harv

 

[Michel Walsh]

Hi,


For a large number of records, the fastest way is to append the ordered
result into a new table, with an autonumber. Just do no specify any value
for the autonumber field (in the append query), but remember to keep the
ORDER BY clause:


INSERT INTO newTableWithAutonum( LastName, FirstName)
SELECT last_name, first_name FROM oldTable ORDER BY last_name, first_name


Get (read) the result from the (temporary) table you created (with an
autonumber).



Hoping it may help,
Vanderghast, Access MVP