1

I want to delete all records with a particular id and category except the one with the max date in MS Access.

Edit: This is how my Data looks like

id category date
1 1 24 June 2021
1 1 20 June 2021
1 2 25 June 2021
1 2 26 June 2021
2 1 24 June 2021
2 1 26 June 2021

And this is how i want my data to look like

id category date
1 1 24 June 2021
1 2 26 June 2021
2 1 26 June 2021

I have this SELECT statement to show me all records I want to keep:

Select
    t1.*
From
    table t1
inner join
    (select max(date) as maxdate, id
     from table
     group by id) t2 on t1.id = t2.id 
                     and t1.date = t2.maxdate

I can't figure out a Delete statement that works in Access.

Manu
  • 11
  • 2
  • 1
    Does this answer your question? [MS ACCESS delete query syntax combined with inner join problems](https://stackoverflow.com/questions/23228073/ms-access-delete-query-syntax-combined-with-inner-join-problems) – diiN__________ Jun 24 '21 at 07:24
  • If i understand you well, you want to remove all records where date is less than max(date). Am i right? – Maciej Los Jun 24 '21 at 07:33

1 Answers1

1

You can use:

delete from t
   where t.date < (select max(t2.date)
                   from t as t2
                   where t2.id = t.id and t2.category = t.category
                  );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786