Last Function

[amg0657]

I ran a Select query using the GROUP BY clause. My query selects only two
fields from one table, one field is [Name] and the second field is [Date].
For example, in the table, [Name] is populated with JohnSmith and there can
be two dates for JohnSmith. The dates are 2/26/2007 and 5/15/2008. For
another name, DaveJones, in the same table, the two dates could be 8/6/2007
and 7/30/2008. If I group by Name and select LastOf[Date], I would expect to
see the following:

Name Date
JohnSmith 5/15/2008
DaveJones 7/30/2008

since 5/15/2008 is after 2/26/2007 (for JohnSmith) and since 7/30/2008 is
after 8/6/2007 (for DaveJones). For some reason, JohnSmith returns 2/26/2007
but DaveJones returns 7/30/2008. Why would this occur? Please advise.
Thanks.

 

[Dirk Goldgar]

I ran a Select query using the GROUP BY clause. My query selects only two
fields from one table, one field is [Name] and the second field is [Date].
For example, in the table, [Name] is populated with JohnSmith and there
can
be two dates for JohnSmith. The dates are 2/26/2007 and 5/15/2008. For
another name, DaveJones, in the same table, the two dates could be
8/6/2007
and 7/30/2008. If I group by Name and select LastOf[Date], I would expect
to
see the following:

Name Date
JohnSmith 5/15/2008
DaveJones 7/30/2008

since 5/15/2008 is after 2/26/2007 (for JohnSmith) and since 7/30/2008 is
after 8/6/2007 (for DaveJones). For some reason, JohnSmith returns
2/26/2007
but DaveJones returns 7/30/2008. Why would this occur? Please advise.

The Last() function doesn't do what you think. It just pulls the value from
the last record it happens to encounter in the input set of records for that
group. In the absence of a set of explicitly sorted source records, it's
not guaranteed to be the record *you* think should be last.

Given that you are looking for the latest date, yuo can get what you want
using the Max() function instead:

SELECT
YourTable.Name,
Max(YourTable.Date) As LastDate
FROM YourTable
GROUP BY YourTable.Name

Note, by the way, that "Name" and "Date" are terrible names for fields, as
they are reserved words. If those are really your field names, you should
change them.

 

[amg0657]

Great, thanks.

BTW, the field names weren't [Name] nor [Date]. I just randomly used those
names randomly because the fields contain names and dates (not using the
aforementioned field names.

Thanks for your help.

A

I ran a Select query using the GROUP BY clause. My query selects only two
fields from one table, one field is [Name] and the second field is [Date].
For example, in the table, [Name] is populated with JohnSmith and there
can
be two dates for JohnSmith. The dates are 2/26/2007 and 5/15/2008. For
another name, DaveJones, in the same table, the two dates could be
8/6/2007
and 7/30/2008. If I group by Name and select LastOf[Date], I would expect
to
see the following:

Name Date
JohnSmith 5/15/2008
DaveJones 7/30/2008

since 5/15/2008 is after 2/26/2007 (for JohnSmith) and since 7/30/2008 is
after 8/6/2007 (for DaveJones). For some reason, JohnSmith returns
2/26/2007
but DaveJones returns 7/30/2008. Why would this occur? Please advise.

The Last() function doesn't do what you think. It just pulls the value from
the last record it happens to encounter in the input set of records for that
group. In the absence of a set of explicitly sorted source records, it's
not guaranteed to be the record *you* think should be last.

Given that you are looking for the latest date, yuo can get what you want
using the Max() function instead:

SELECT
YourTable.Name,
Max(YourTable.Date) As LastDate
FROM YourTable
GROUP BY YourTable.Name

Note, by the way, that "Name" and "Date" are terrible names for fields, as
they are reserved words. If those are really your field names, you should
change them.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)