Using LAST but still getting all records??

[scott0_1]

Hello all,

I have the following query where ID is an Autonumber:

SELECT Last(tblActingTMP.ID) AS LastOfID, tblActingTMP.EmpName, tblActingTMP.SubstantiveCC, tblActingTMP.SubstantivePN, tblActingTMP.ActingForName, tblActingTMP.PositionNumber, tblActingTMP.ActingCC, tblActingTMP.StartDate, tblActingTMP.EndDate, tblActingTMP.Excluded
FROM tblActingTMP
GROUP BY tblActingTMP.EmpName, tblActingTMP.SubstantiveCC, tblActingTMP.SubstantivePN, tblActingTMP.ActingForName, tblActingTMP.PositionNumber, tblActingTMP.ActingCC, tblActingTMP.StartDate, tblActingTMP.EndDate, tblActingTMP.Excluded;

The query should only return the last record if I'm not mistake, but it is returning all records in the table. What am I doing wrong here? I've also tried MAX.

Thanks,

Scott

[pbaldy]

It will return all the unique combinations of the fields in the GROUP BY clause, with the LAST ID value. If you want the last record:

SELECT TOP 1 *
FROM tblActingTMP
ORDER BY ID DESC

[scott0_1]

It will return all the unique combinations of the fields in the GROUP BY clause, with the LAST ID value. If you want the last record:

SELECT TOP 1 *
FROM tblActingTMP
ORDER BY ID DESC

That got it! Thanks! I've not seen the TOP function before.

[pbaldy]

Happy to help!