Ken's solution is about returning fields of the record WHERE a minimum value
is found, as well as in http://www.mvps.org/access/queries/qry0020.htm
presenting 4 solutions.

My intervention is about the general claim that FIRST and LAST are totally
useless.


My intervention is not about how to return fields of the record where a
minimum value is found. In fact, at
http://www.mvps.org/access/queries/qry0020.htm, solution number 3 uses FIRST
in the perspective of my intervention: it is a VERY useful aggregate when we
want some values, from fields (more than one) where something occur an which
imply a GROUP BY syntax.


The two interventions, Ken and mine, are distinct.


Vanderghast, Access MVP



"Bob Barrows" wrote in message
...
vanderghast wrote:
How can you claim with authority that they are not good if you are
not even sure about how they should work in the first place?

Min and Max can replace First and Last if there is just one field
aggregated with First, or Last, but not if there is more than a
single field so aggregated. How could you replace FIRST in the
following query, with MIN / MAX:

SELECT f1, FIRST(f2), FIRST(f3) FROM table GROUP BY f1

where the goal of that statement is to get a (one) record, for each
group value.
Considering the data:

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


(just for illustration, but someone should not think that this data
represent all possibilities).

And come with a simple, or not, solution, implying MIN (or MAX)... or
change your claim about FIRST/LAST "shouldn't even be an option to
use".

Well of course, in Access, given this dataset, we would have to advise
using FIRST, since there appears to be no field that can be used to
provide the sort order for this data, besides your whim when you set it
up. I would also have to add the proviso: don't be too shocked if FIRST
does not return the "correct" results someday. There is no guarantee that
the database engine will not choose to return the results in some other
order someday.

If there was a field or fields that could be used to provide this
particular order, then I would offer some variation of Ken's advice.


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"