Finding the First or Last Record in a Query in Access 97/2000

In Microsoft® Access, you can find the first or last record in the result set returned by a query by clicking First or Last in the Total row in the query design grid. However, the First and Last functions may appear to return arbitrary records because they don't take into account indexes or sort orders. Records are returned in chronological order - the order in which they were entered into the table. This isn't necessarily the same as alphabetical order, so these functions may or may not return the first or last record alphabetically.

In Access 97 and 2000, you can display the first or last record in a query by organizing the data in a predictable sequence and then selecting the record to be displayed. The following steps demonstrate this process by finding the last record alphabetically in the Customers table in the Northwind sample database.

Note  The default location of the Northwind sample database is the Program Files\Microsoft Office\Office\Samples folder. If you don't see Northwind.mdb in your Samples folder, or if you need more information about opening Northwind, type open the northwind sample database in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

1. In the Database window, click Queries under Objects, and then click New.
2. In the New Query dialog box, click Design View, and then click OK.
3. In the Show Table dialog box, click Customers, click Add, and then click Close.
4. In the field list, double-click CustomerID.
5. In the first column of the query design grid, click the Sort cell, and then click Descending.
6. In the Top Values box on the Query Design toolbar, type 1.
7. On the Query menu, click Run.

The value returned should be WOLZA, which is alphabetically the last CustomerID in the table.