Deleting Records

[Andy Bailey]

I have a table like this:-

ID RevNo Other Fields Generated
1 0 a 26/03/07 15:00:00
1 0 b 26/03/07 15:00:00
1 0 c 26/03/07 15:00:00
1 0 a 26/03/07 15:15:00
1 0 b 26/03/07 15:15:00
1 0 c 26/03/07 15:15:00
1 0 a 26/03/07 15:30:00
1 0 b 26/03/07 15:30:00
1 0 c 26/03/07 15:30:00
1 1 a 26/03/07 16:00:00
1 1 b 26/03/07 16:00:00
1 1 a 26/03/07 16:10:00
1 1 b 26/03/07 16:10:00
2 0 a 26/03/07 17:00:00
2 0 b 26/03/07 17:00:00
2 0 a 26/03/07 17:10:00
2 0 b 26/03/07 17:10:00
2 1 a 26/03/07 18:00:00
2 1 b 26/03/07 18:00:00
2 1 a 26/03/07 18:10:00
2 1 b 26/03/07 18:10:00


I'm trying to cobble together a delete query to delete all records for a
given ID and RevNo except for the one with the latest Generated timestamp.
With the table above it would leave me with:-

ID RevNo Other Fields Generated
1 0 a 26/03/07 15:30:00
1 0 b 26/03/07 15:30:00
1 0 c 26/03/07 15:30:00
1 1 a 26/03/07 16:10:00
1 1 b 26/03/07 16:10:00
2 0 a 26/03/07 17:10:00
2 0 b 26/03/07 17:10:00
2 1 a 26/03/07 18:10:00
2 1 b 26/03/07 18:10:00


I'm using Access 2000 and I'm a dangerous novice. I need to get an idiot's
guide to SQL I think.

Any help with this would be gratefully appreciated.

Thanks in advance.

Andy

 

[Allen Browne]

Use a subquery to identify the latest date, and delete all the others.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

(You will need a primary key in this table to ensure the correct records are
deleted.)

 

[Andy Bailey]

Allen

Many thanks for the help and the link. This will help me with a lot of other
things I'm tinkering with.

Your web site is excellent - extremely useful.

Kind regards


Andy

 

[455656]

Andy Bailey escribió: