Faiure of LAST function in in a Query

[Guest]

Using Access 2003, I use LAST in a query to return the most recent Record
number (and use that in another query to produce the most recent record, as
the Access Help illustrates for finding the last record entered.) However,
incorrect results happened when the number of records got to 130: Although
there have been more than 130 records entered, the result of the query has
stopped at 130. (Due to deleted records, there are fewer actual records ion
the database.) Use of the MAX function applied to the autonumbered Record
Number is currently returning the expected value and so is a substitute, but
the question of why the LAST function would fail remains. Under what
circumstances will the LAST function in a query fail, and is there a
suggested remedy so as to be able to continue to rely on the LAST function?

 

[Guest]

Both First and Last depend on the order of the records. If the query is not
sorting by the field or fields where first or last record have meaning,
neither First or Last are dependable. I avoid using them.

Max or Min are best especially if you have a Date field storing both the
date and time of the record. No sorting required.

Using Max on an autonumber field should be OK; however, there are
circumstances where someone could reintroduce a previously used, but since
deleted, number into the autonumber field with an append query. Actually
there's nothing to stop Access from even putting in a negative or other
non-sequential number into the autonumber field. Autonumbers are only
garenteed to be unique when entered.

 

[Michel Walsh]

Hi,


First and Last have their use even for unsorted records, since, say, First,
will pick the fieldS, with an S, aggregated with First, from the SAME
record. Example, if you have

f1 f2 f3
1 20 20
100 1 1
0 2 100


then

SELECT MAX(f1), MAX(f2), MAX(f3) FROM mytable

will return

100 20 100

but

SELECT FIRST(f1), FIRST(f2), FIRST(f3) FROM mytable

is to return

1 20 20

or

100 1 1

or

0 2 100



all picked fields with FIRST coming FROM the SAME record.


LAST pick a different record than FIRST if there are more than one record
for a given GROUP.



Hoping it may help,
Vanderghast, Access MVP