Most efficient way to get 1000 most recent rows

[Guest]

I need a query to pull the most recent n rows from a table. I figure the most
efficient way must be to use the autonumber key since that is indexed. So I
need to get the highest key then all records prior to that up to a certain
number (say 1000).
How do I do that query (in SQL not the builder)?

 

[Guest]

SELECT TOP 1000 ...
.....
ORDER BY AutonumberField DESC;

 

[Jeff Boyce]

Access Autonumbers are not guaranteed to be sequential, and if, perchance,
they are set to (or become) random assignment, you are NOT likely to get the
"last" 1000 rows.

Is there a date/time field in the table definition? Use that to sort on,
and use the TOP property of the query to limit how many rows are returned.

Regards

Jeff Boyce
Microsoft Office/Access MVP