Select all rows except the last

[XelaIrodavlas]

Hi all,

Can anyone advise how I might select all the records within a query except for the most recent one? I have an AutoNumber ID field and a date stamp to use , but I can't figure out how to exclude just the biggest record.

In a nutshell, I am trying to run an update query on the After Insert Event, so that when the user inserts a new record it will mark down similar older records as 'superceded'.

At the moment I can select all the records, and I can select just the largest one, but I can't seem to take one away form the other.

Am I missing something stupid...?

If it helps here is the SQL in it's current state:

 UPDATE [Personnel-Qualifications] SET [Personnel-Qualifications].[Superceded?] = True
WHERE ((([Personnel-Qualifications].PersonnelID)=[Forms]![Personnel]![PersonnelID]) AND (([Personnel-Qualifications].QualificationID)=[Forms]![Personnel]![Child21]![qualificationID]));

Any thoughts appreciated!

thanks,

 

[XelaIrodavlas]

Might have just solved this one myself... in case anyone else needs to know I added another Condition on the append query to find records where the Auto ID was < the ID of the latest one, as referenced in the form.

In SQL:

UPDATE [Personnel-Qualifications] SET [Personnel-Qualifications].[Superceded?] = True
WHERE ((([Personnel-Qualifications].PersonnelID)=[Forms]![Personnel]![PersonnelID]) AND (([Personnel-Qualifications].QualificationID)=[Forms]![Personnel]![Child21]![qualificationID]) AND (([Personnel-Qualifications].PQID)<[Forms]![Personnel]![Child21]![pqid]));