First and Last work as documented and by no mean imply Earliest, Latest, or
anything similar. Their main use is to get an aggregate value by returning
value from a (one) record of the group.

Assuming the fields and values:

f1 f2 f3
10 1 100
10 50 1
10 100 50


then the statement: SELECT f1, FIRST(f2), FIRST(f3) FROM table GROUP BY f1
returns one of the three record.

On the other hand, SELECT f1, MIN(f2), MIN(f3) FROM table GROUP BY f1
returns 10, 1, 1, which is NOT a 'record' in the original set,
and so does: SELECT f1, MAX(f2), MAX(f3) FROM table GROUP BY f1,
returning 10, 100, 100 also not a 'record' in the original set.
while SELECT f1, LAST(f2), LAST(f3) FROM table GROUP BY f1 would return a
record from the original set (which one is unpredictable, without more
information).


LAST would return (values from) a different record than FIRST if there is
more than one record in the group.



Vanderghast, Access MVP

"david" wrote in message
...
First and Last work just well enough to be dangerous,
and are never safe.

There are two problems:
1) The default order is primary key order, then any new
records in the order you added them since the last time
you did a compact or repair . That works alright only as
you add records in primary key order. If you add records
out of order, or go back and add change old records,
your order is mixed up.
You can get around that by using a sorted query, but....

2) In a complex Sorted query, the Sort may not be done
in the correct place, and the First and Last selection may
happen using the wrong Sort order.

Access 2.x didn't have problem 1, so naive users didn't
have a problem with First and Last in Access 2.x

Problem 2 is a bug, and didn't emerge until later. When
it did emerge, MS didn't fix it: instead they amended
the help files to say that First and Last did not always
return the First or Last values.

Then MS fiddled with the help files, and made them
unusable, so you have to come here for help. Now MS
is closing these NewsGroups...

(david)

"Dan" wrote in message
...
For some reason First and Last are giving the wrong data in a Totals
query. I assume that somehow the data is not ordered correctly in the
main table, but it does show correctly in the datasheet view. For
example if I group the sample database below by Name and Year, First
of Location should be B, unfortunately it shows up in the query as
A.

Name Year Stint Location
Ed 1999 1 B
Ed 1999 2 A
Ed 2000 1 A

Desired Result
Name Year Stint Location
Ed 1999 1 B (But shows up as A)
Ed 2000 1 A

Any ideas from someone who has seen this before? This is the first
time I have seen this--I created the master table under a make tables
query. Could it be that even though it comes out in the right order
in the datasheet view it is actually ordered differently? If so is
there a way to internally reorder a table?

--Dan L.