Retrieve the last record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI, I want to get the last record on a query using the date field to be used
forward on a report.
Im trying using the last() funcion as criteria but I get an error.

Any comment is welcome
Thanks!!
 
Hi:

One way to do this to

Select Top 1 [Field List] From
Order by [Field] Desc

This selects the Top 1 record only - since you Order by a Field Desc which
would normally be ascending you should get the last record.

Regards,

Naresh Nichani
Microsoft Access MVP
 
Naresh said:
One way to do this to

Select Top 1 [Field List] From
Order by [Field] Desc
Click to expand...


Be aware the MS-proprietary TOP N syntax is based on a cursor under the
covers i.e. can give odd results and the reliance on the ORDER slows
things down. What about this:

SELECT MyKeyCol
FROM MillionRowTable
WHERE MyDateCol = (
SELECT MAX(T1.MyDateCol)
FROM MillionRowTable T1
);

Jamie.

--
 
Thanks!! Naresh, Jamie both queries work



onedaywhen said:
One way to do this to

Select Top 1 [Field List] From
Order by [Field] Desc
Click to expand...


Be aware the MS-proprietary TOP N syntax is based on a cursor under the
covers i.e. can give odd results and the reliance on the ORDER slows
things down. What about this:

SELECT MyKeyCol
FROM MillionRowTable
WHERE MyDateCol = (
SELECT MAX(T1.MyDateCol)
FROM MillionRowTable T1
);

Jamie.
Click to expand...
 
Back
Top