Re: First of and last of help!

From: John Vinson (jvinson_at_STOP_SPAM.WysardOfInfo.com)
Date: 09/06/04 

Date: Mon, 06 Sep 2004 12:46:01 -0600

On Mon, 6 Sep 2004 16:27:52 +0100, "C Tate"
<colin@nobodyhere.mrcrtate.fsnet.co.uk> wrote:

>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!).

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?

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)

Relevant Pages

  • Re: White Paper on How MSAccess interacts with SQL Server Back End?
    ... this newsgroup is about ADP and has nothing to do with ODBC linked ... For simple queries, Access will create a TSQL queries that will run on the ... these keys to retrieve the other fields from the tables by group of 10 rows. ... access path, hands it off os SQL2K, how SQL interprets it and then ...
    (microsoft.public.access.adp.sqlserver)
  • Re: hibernate question
    ... Daniel wrote: ... > queries to retrieve the data run fine, but every time I try to save ... The hibernate.show_sql config option is the easiest way to ...
    (comp.lang.java.programmer)
  • Re: Many-to-many self-relationships
    ... This would have the disadvantage of needing a union of two queries whenever you needed to retrieve all the links connected to a particular Person. ... Rick Bowlby ...
    (comp.databases)
  • Re: Soft Delete Records (to an archive)
    ... The simplest way to do this is not to bother. ... Instead, just add to the table a Date/Time field called DateDeleted, ... with a defalt value of NULL. ... Modify the queries behind your forms etc. ...
    (microsoft.public.access.formscoding)
  • Re: Listing objects (tables and queries) within a query
    ... I have a number of queries that have a problem with a join ... local MS-Access table and the other is a linked SQL Server db table. ... I need to retrieve the matched records somehow. ...
    (microsoft.public.access.modulesdaovba)
Loading