first or last of record set

[Guest]

OK, I can accomplish the following by creating a table and them use the
"Last" or "First" criteria and the "Total" row in query design, but when I
try to accomplish this in the actual query I get a random return. I think it
has to do with Access not recognizing my sort order.

Here goes:
I need my query to return the last records of this query:

ID Date
100 01/12/05
100 02/03/05
100 05/10/05
200 02/03/05
200 11/01/05

Results:
100 05/10/05
200 11/01/05

Thanks in advance

 

[Michel Walsh]

Hi,

Last and First are poorly choosen names, they do not refer to the
Earliest neither to the Latest record at all, but, for all practical
purpose, to random records. Take a look at
http://www.mvps.org/access/queries/qry0020.htm for some examples (like to
answer to "how is the last one to have borrow each book").



Hoping it may help,
Vanderghast, Access MVP

 

[Mike Schlosser]

I do not believe First and Last return random results, but rather the
First record entered and the last record entered into the table, meeting any
criteria, of course, but ignoring any sort order..

Min and Max return sorted order results.

Mike Schlosser

 

[Mike Schlosser]

Reverse your sort order and return only the top 2 records.

The TOP command can be set to a number or percent of the total of the
returned records.
Select top 10 ' returns top 10 records meeting the criteria.
Select top 10 Percent ' returns top 10 percent of the records meeting the
criteria.

DESC reverses the sort on the field to Decending Order.

SELECT TOP 2 ID,Date
FROM MyTable
ORDER BY ID DESC, Date DESC;

Mike Schlosser

 

[Guest]

Min or Max works! Thanks

 

[Michel Walsh]

Hi,


Nope. They return the first/last record seen accordingly to the
execution plan Jet will use, at that moment. Since that query execution plan
depends on various details, it is like random. It is not the first/last
entered in the table, there is no such data stored in the table and records
can physically move in the database as new data is entered, or old records
deleted, or modified.



Vanderghast, Access MVP