How to delete all records in an access table except most recent

Question

I have a table like:

name  -  log  -  date        -  dataFields    
john  -  2    -  21/03/2014  -  123  
john  -  2    -  22/03/2014  -  345  
john  -  2    -  23/03/2014  -  234  
karl  -  2    -  20/03/2014  -  789  
liam  -  1    -  20/03/2014  -  135  
liam  -  2    -  21/03/2014  -  321  
mick  -  1    -  20/03/2014  -  987  
mick  -  1    -  21/03/2014  -  123  
mick  -  2    -  22/03/2014  -  456  
mick  -  3    -  20/03/2014  -  789  
mick  -  3    -  24/03/2014  -  456  

I want to delete all records except the latest name/log combination. So in the example I'm left with

john  -  2    -  23/03/2014  -  234  
karl  -  2    -  20/03/2014  -  789  
liam  -  1    -  20/03/2014  -  135  
liam  -  2    -  21/03/2014  -  321  
mick  -  1    -  21/03/2014  -  123  
mick  -  2    -  22/03/2014  -  456  
mick  -  3    -  24/03/2014  -  456  

I've tried using a subquery that selects the records I want to keep. Then using not exists to select the records I want to delete, but it doesn't select any records. Can I use not exists in this way?

Answer 1

Wouldn't :

Delete from [TableName] as T1
Where Exists(
SELECT [log],[name],Max([date]) FROM [TableName] 
Where [log] = T1.[log]
And [name] = T1.[name]
Group By [log],[name]
Having T1.[date] < Max([date]))

do the trick?

Answer 2

Thanks Alexander, but the create table didn't work. So what I've done is create a subquery that returns the max(date) for each name/log group. Then used this subquery in another query that has a left join on name/log/date from the original table. And just delete records from the original table where name is null (ie there's no matching record found in the subquery with the same name/log/date because there's a later record there)