Last record search not working

[Dave Couch]

Access 2003. I have a table with 1831 records with a last key index value
(ProposalID) of 1831. I have set up a query to select the last record in the
table. I am using the "Last" selection in the Total row. When I run the
query, it will only show record 1816, not the last record. I have tried
recreating the query with no luck. All of the data in the fields looks
correct and the ID field is an "autonumber" field. I have used this
procedure on other tables with success. I can't figure out what is wrong
with this table. I did a "compact and repair" on the database. Any ideas
out there?

Dave

 

[Douglas J. Steele]

Do you have an ORDER BY clause in your query? Without it, Last will
essentially return a random record. As it says in the Help file "Because
records are usually returned in no particular order (unless the query
includes an ORDER BY clause), the records returned by these functions will
be arbitrary."

It sounds like you should be using Max instead of Last.

 

[Dave Couch]

I guess I didn't see the note in the help file. I have other queries that
"Last" seems to be working on. Oh, well, "Max" seems to work just fine, so I
will go with that. Thank you.

 

[david]

I have used this procedure on other tables with success.

That's the trouble: LAST is just reliable enough to trick you into thinking
it works, then BAM ... It may have been the compact and repair which
resorted the recordset: is there also a primary key field? The compact
and repair was always going to happen one day, but even if you don't
have another primary key, one day you were going to find a query that
lost the natural sort order and gave this problem.

Worse, the help is misleading: LAST never works properly even if
you specify a sort order. The problem isn't really that recordsets have
an undefined sort order: if you are getting your records from an MDB,
the records always, as it happens, have a well defined sort order,
although not always what you expected.

The intractable problem is that LAST is not always applied in the
correct place in a complex set of subqueries: sometimes the query
plan builder puts it in the wrong place, before the relevant sort has
been done, or after the records have been joined to another table.

(david)