How to get first() working?

  • Thread starter Thread starter Not Me
  • Start date Start date
N

Not Me

Hi,

When using the first() function, either on a table directly or on a ordered
table, the results are the same.

Why is this? I thought it could be used on ordered results to pick the first
record of many groups?

Please help,
Chris
 
Chris

Your definition may not be the same as Access' definition of "first". If
you check the HELP listing, it mentions that Access uses (?an internal)
datetimestamp to determine when a record was added. Apparently, First() and
Last() use that. I've found that using a SQL-Server backend, however, seems
to force First() and Last() functions to act as Min() and Max().

Good luck!

Jeff Boyce
<Access MVP>
 
Jeff Boyce said:
Chris

Your definition may not be the same as Access' definition of "first". If
you check the HELP listing, it mentions that Access uses (?an internal)
datetimestamp to determine when a record was added. Apparently, First() and
Last() use that. I've found that using a SQL-Server backend, however, seems
to force First() and Last() functions to act as Min() and Max().
Click to expand...

Thanks for your reply, that sounds like it's where the problem lies,
although in my Access help it says:

***
The First and Last functions are analogous to the MoveFirst and MoveLast
methods of a DAO Recordset object. They simply return the value of a
specified field in the first or last record, respectively, of the result set
returned by a query. Because records are usually returned in no particular
order (unless the query includes an ORDER BY clause), the records returned
by these functions will be arbitrary.
***

Are they talking about the same thing? I'm using Access 2002.

Chris
 
Yeah, I saw that too, but something I ran across recently (and can't
re-find) mentioned that using First() on Access tables gets you the first
record entered, but on SQL-Server tables does not. Give this try for
yourself and see if it's what's happening with your data...

Create a new table, with a single field (numeric). In the first 10 rows,
enter 10, 9, 8, ...

Create a query on the table, select your single field, click on the
summation/totals button, and select First. Run it. If it works the same
way it does here, it will return "10" (the first value added
chronologically). Now change the groupby to Min() and run it -- you'll get
"1", the smallest value.

Sorry I can't point you to the reference right now...

Good luck

Jeff Boyce
<Access MVP>
 
Yeah, I saw that too, but something I ran across recently (and can't
re-find) mentioned that using First() on Access tables gets you the first
record entered, but on SQL-Server tables does not. Give this try for
yourself and see if it's what's happening with your data...
Click to expand...

Actually my understanding (which may be incorrect!) is that First()
returns the first record *in disk storage order*. Often, maybe almost
always, this order will in fact be the order in which the records were
entered, but my understanding is that if there are deleted records in
a table, that new records can occupy the freed-up space; therefore the
most recently entered record (or ANY record) might end up being the
First().
 
Jeff Boyce said:
Yeah, I saw that too, but something I ran across recently (and can't
re-find) mentioned that using First() on Access tables gets you the first
record entered, but on SQL-Server tables does not. Give this try for
yourself and see if it's what's happening with your data...

Create a new table, with a single field (numeric). In the first 10 rows,
enter 10, 9, 8, ...

Create a query on the table, select your single field, click on the
summation/totals button, and select First. Run it. If it works the same
way it does here, it will return "10" (the first value added
chronologically). Now change the groupby to Min() and run it -- you'll get
"1", the smallest value.

Sorry I can't point you to the reference right now...
Click to expand...

Cheers, I ended up using max and a few variations to get it right. Only took
all day!

Cheers,
Chris
 
Thanks, John. The (now in-hiding) explanation I saw made it seem that
Access used some form of internal timestamp, but I like your explanation as
well. Can you point me to the reference source on yours?!

Jeff Boyce
<Access MVP>
 
Thanks, John. The (now in-hiding) explanation I saw made it seem that
Access used some form of internal timestamp, but I like your explanation as
well. Can you point me to the reference source on yours?!
Click to expand...

Not really - just half-remembered things I picked up in this newsgroup
over the years.
 
Hi,

Which is also mine, First and Last indicates more or less a random
record (in the group). They differ from Min or Max when you kick more than
one field.


f1 f2 ' fields
10 100
50 1
100 50
1 60 ' data

Min(f1), Min(f2) return 1, 1
Max(f1), Max(f2) 100, 100

but the problem can be than (1, 1) is not a real record, neither (100,
100);

Last(f1), Last(f2) could return any of the four records, but from the
same record.


Last, and First, should not be seen as having any meaning of Latest or
Earliest. They probably come "from the loop", accordingly to the execution
plan devised by the engine (table scan for small tables, accordingly to the
index on the GROUPing fields, ... )



Vanderghast, Access MVP
 
Back
Top