First of and last of help!

  • Thread starter Thread starter C Tate
  • Start date Start date
C

C Tate

I am a little confused by the 'first of' and 'last of' options when using
the totals in queries (despite having done a search in this group and
reading the relevant part of an Access book!).

I understand that 'last of' for example will take the last 'physical'
record. I'm not totally sure what that means. Does it mean the last record
in the table? So, if you took an orders table and queried on a particular
customer, you would get the last record in that table, not necessarily the
most recent date?

Is there anywhere I can read a good description of what these options
actually do, preferable with examples?!?!
 
I am a little confused by the 'first of' and 'last of' options when using
the totals in queries (despite having done a search in this group and
reading the relevant part of an Access book!).
Click to expand...

They ARE confusing.
I understand that 'last of' for example will take the last 'physical'
record. I'm not totally sure what that means. Does it mean the last record
in the table? So, if you took an orders table and queried on a particular
customer, you would get the last record in that table, not necessarily the
most recent date?
Click to expand...

Not really. Access stores records on your hard disk drive, in a
sequential order. However, the most recently added record is not
necessarily in the last physical "slot" on the disk. It often will be,
but if there is room elsewhere Access will store a new record there.
In addition, the physical order of records gets rearranged if you
compact the database.

Essentially, the First() operator is useful ONLY if you want to
retrieve an arbitrary record's value, and you don't care which. I have
not been able to think of any good reason to use Last() since it also
retrieves an arbitrary, and uncontrollable, record - and must traverse
the entire recordset to do so!

If you want the most recently entered record, you must include a
date/time field in the table and use a query searching for the maximum
value of that date field; you are correct that getting the "last"
record is NOT a reliable way to do this.

John W. Vinson[MVP]
(no longer chatting for now)
 
I THINK I understand what you are saying, but am not sure! Are you saying
that using the first/last operator might really just return a 'random'
record?

This is quite a revelation for me. The database I work with at work (not
designed by me!) seems to use first/last operator alot. It seems the person
who made the queries thought that 'last of' would return the most recent
date a client entered accommodation (the database contains info on homeless
people). When I look at the query, it does indeed look like a meaningless
lot of pap - totally random dates as far as I can see.

In the circumstances do you think there is any real use at all for these
operators?!
 
An argument can be made that there's a point to First, but it's not a
particularly strong one (it lets you check that at least one record is
found, but so does SELECT TOP 1). I've yet to hear a convincing argument for
using Last (and if there is one, it could also be met using SELECT TOP 1
with a different sort order)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Somewhere, I've read the First aggregate function is sometimes more
efficient than GroupBy. I mean, for instance, if you want to sum Field3 in a
table like following:

Field1 Field2 Field3

A C 1
A C 2
A D 3
B E 4
B F 5
B F 6

First GroupBy Sum <--- This is better/more
efficient to perform a Totals query
GroupBy GroupBy Sum <--- than this

Am I wrong?


Tonín
 
I've never heard that stated before, but that doesn't mean it's wrong.

However, it certainly isn't standard SQL, so even if it works with Access,
it may not with any other DBMS

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Back
Top