First and Last Problem

[Dan]

For some reason First and Last are giving the wrong data in a Totals
query. I assume that somehow the data is not ordered correctly in the
main table, but it does show correctly in the datasheet view. For
example if I group the sample database below by Name and Year, First
of Location should be B, unfortunately it shows up in the query as
A.

Name Year Stint Location
Ed 1999 1 B
Ed 1999 2 A
Ed 2000 1 A

Desired Result
Name Year Stint Location
Ed 1999 1 B (But shows up as A)
Ed 2000 1 A

Any ideas from someone who has seen this before? This is the first
time I have seen this--I created the master table under a make tables
query. Could it be that even though it comes out in the right order
in the datasheet view it is actually ordered differently? If so is
there a way to internally reorder a table?

--Dan L.

 

[ghetto_banjo]

First and Last do NOT work the way they think you do. I believe they
try to see the first and last records written to disk or something,
but the results are NEVER good. They shouldn't even be an option to
use in Access.


Instead, use Min and Max, they will work in the manner you would
expect.

 

[david]

First and Last work just well enough to be dangerous,
and are never safe.

There are two problems:
1) The default order is primary key order, then any new
records in the order you added them since the last time
you did a compact or repair . That works alright only as
you add records in primary key order. If you add records
out of order, or go back and add change old records,
your order is mixed up.
You can get around that by using a sorted query, but....

2) In a complex Sorted query, the Sort may not be done
in the correct place, and the First and Last selection may
happen using the wrong Sort order.

Access 2.x didn't have problem 1, so naive users didn't
have a problem with First and Last in Access 2.x

Problem 2 is a bug, and didn't emerge until later. When
it did emerge, MS didn't fix it: instead they amended
the help files to say that First and Last did not always
return the First or Last values.

Then MS fiddled with the help files, and made them
unusable, so you have to come here for help. Now MS
is closing these NewsGroups...

(david)

 

[vanderghast]

First and Last work as documented and by no mean imply Earliest, Latest, or
anything similar. Their main use is to get an aggregate value by returning
value from a (one) record of the group.

Assuming the fields and values:

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


then the statement: SELECT f1, FIRST(f2), FIRST(f3) FROM table GROUP BY f1
returns one of the three record.

On the other hand, SELECT f1, MIN(f2), MIN(f3) FROM table GROUP BY f1
returns 10, 1, 1, which is NOT a 'record' in the original set,
and so does: SELECT f1, MAX(f2), MAX(f3) FROM table GROUP BY f1,
returning 10, 100, 100 also not a 'record' in the original set.
while SELECT f1, LAST(f2), LAST(f3) FROM table GROUP BY f1 would return a
record from the original set (which one is unpredictable, without more
information).


LAST would return (values from) a different record than FIRST if there is
more than one record in the group.



Vanderghast, Access MVP

 

[vanderghast]

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".



Vanderghast, Access MVP

 

[Bob Barrows]

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.

 

[John Spencer]

It can be done using a variety of methods. Easiest to understand is using
nested queries. Assumption you use Stint to determine first and last.

Query 1: Get first (minimum) stint per name and year. Save as qFirstStint
SELECT [Name], [Year], Min(Stint) as FirstStint
FROM [Your Table]
GROUP BY [Name], [Year]

Query 2: Use query1 and the original table
SELECT [Your Table].[Name], [Your Table].[Year], [Your Table].Stint
, [Your Table].Location
FROM [Your Table] INNER JOIN qFirstStint
ON [Your Table].[Name] =qFirstStint.[Name]
AND [Your Table].[Year]=qFirstStint.[Year]
AND [Your Table].Stint = qFirstStint.FirstStint

Ken Sheridan's posted solution is very similar. It has the advantage of
returning records that can be updated. Its disadvantage is that with large
recordsets it may be considerably slower than the above solution.

Oh! The above can be done in a single query if your table and field names
follow the naming guidelines. The names should consist of at least one
letter, numbers, and the underscore character. No other characters allowed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[vanderghast]

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

 

[david]

while SELECT f1, LAST(f2), LAST(f3) FROM table GROUP BY f1 would return a

record from the original set (which one is unpredictable, without more
information)

Which one is unpredictable even with more information.

First and Last work as documented. That is, MS changed
the documentation for A97 to match the behaviour of
First and Last.

(david)