Aggregate Functions "First" and "Last"

[jayceejay]

I can't seem to find any information pertaining to the aggregate Totals
functions "First" or "Last". All I can find is, "Displays the first/last
record in a list. Could someone tell me why I might need such information, or
how, in general, either of these two functions could benefit me? I would be
most grateful.

Jay

 

[John Vinson]

I can't seem to find any information pertaining to the aggregate Totals
functions "First" or "Last". All I can find is, "Displays the first/last
record in a list. Could someone tell me why I might need such information, or
how, in general, either of these two functions could benefit me? I would be
most grateful.

Jay

First isn't much use, and Last is of no use at all that I've been able
to ascertain. They return the first and last records *IN DISK STORAGE
ORDER* - an order which you cannot easily control or change.

I'll use First() in a totals query where I know there will just be one
record, or where it doesn't matter which record is to be shown. I've
never found any circumstance where Last() is useful.

John W. Vinson[MVP]

 

[jayceejay]

John, I truly appreciate your candor! I await any other responses from other
users, but if nothing else, John, you have restored my sense of sanity.

 

[jayceejay]

Something just ocurred to me: Would i bepossible to use "First" on the
criteria line of a query? Find all orders WHERE Item # Field First = Item
#77563?

 

[Douglas J. Steele]

First isn't much use, and Last is of no use at all that I've been able
to ascertain. They return the first and last records *IN DISK STORAGE
ORDER* - an order which you cannot easily control or change.

I'll use First() in a totals query where I know there will just be one
record, or where it doesn't matter which record is to be shown. I've
never found any circumstance where Last() is useful.

As I read the Help, John, it will return the First or Last in the sorted
order when used in conjunction with an ORDER BY clause. However, Min or Max
is just as good.

 

[Douglas J. Steele]

No. You could have a subquery that used First (or Last) to return a value,
and then use that value in your Where clause, but you cannot use First the
way that you've shown.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[jayceejay]

Thanks a million for your help, gentlemen.
Jay

No. You could have a subquery that used First (or Last) to return a value,
and then use that value in your Where clause, but you cannot use First the
way that you've shown.

 

[Guest]

Last returns the field value from 'Last' record in a recordset formed
from an aggregate query: We used to use it for returning the last of a
historical series up to a given date:

select Last(idxAudit) as idx, idxRecord from tbl
where dt < #1/1/2000#
group by idxRecord

However, it was unreliable, and in recognition of this, for years MS
has only said 'returns a random value'

A Jet table with an ascending autonumber primary key will always
be sorted in primary key order, and so should have a first and
a last. A query with a specified sort order should also have a
first and a last (for the whole recordset, and for any defined subset).

Unfortunately, in evaluating complex queries, First and Last did not
always applied to the correctly sorted set. Sometimes, First or
Last really did return a random value. Even though you could see
that the recordset was correctly sorted when Selected, when you
applied the grouping, First and Last returned the wrong values.

We became aware of this when we started using A97, and the help
files(not worthless then as it is now) warned us off, but in fact, once
we started looking, we saw the problem in A2 (Jet 2.5). So in fact
I don't remember if we ever actually saw the problem with Jet 3.5,
and I know we never saw the problem with Jet 4.0, because we
never use First and Last that way anymore.

So AFAIK, Jet 4.0 is the same as Jet 2.5: First and Last appear to
select values from the First and Last records, using the sort order
you have specified or the 'default sort order' of the table, but rarely
the query plan is out of order, and you get a random record instead.

We still use First where we know all the values in a group are identical,
or irrelevant, on the basis that it must be faster and easier than using
MAX.

(david)