Delete not in top 5

[davedynamic]

I am trying to write a delete query and it is driving me b......s.

I have a table which contains date,address, FAC,pH,TA etc.

I run a query to select the top 5 by date.

I now want to delete the records not in the top 5. But I do not know where to start to write this. I have tried fiddling around with a query and just don't seem to be able to get anything that will run.

 

[raskew]

(1) Create a query that selects all of the records.

(2) To return the records that aren't in the TOP 5, set the criteria for your date field to:

NOT IN (SELECT TOP 5 ……………..) i.e., your previously created TOP 5 query.

(3) You'll probably have to modify your TOP 5 query to include only the date field.

(4) Once you have this working, turn it into a delete query.

 

[davedynamic]

Thanks for the help