Query - Remove old entries

[Number11]

So we have a table that gets updated every day with the last events. like a log on the processing of any order

The field that gets updated is called [Order_Status] and the date gets updated within the [ImportingDate] - what i need to do is once completed delete (or archive) all earlier records so just leaves the "Completed" entry - how can this ne done from a query please

ORDER_No	OrderStatus	ImportingDate
148774	Completed	26/01/2022​
148774	Picking	24/01/2022​
148774	Assigned	23/01/2022​

 

[arnelgp]

Suggest tou add autonumber field.
As much as possible to all tables.
There must be a better way than this:

Delete * from Table1
Where (Select Count("1") From Table1 As T Where T.Order_No = Table1.Order_No And OrderStatus="Complete")<>0
And ID <> (Select ID From Table1 As Y Where Y.Order_No = Table1.Order_No And OrderStatus = "Complete")

 

[June7]

I would leave the records and just apply filter. Never know, someday knowing when an order was in 'picking' status may be requested.

 

[Number11]

I would leave the records and just apply filter. Never know, someday knowing when an order was in 'picking' status may be requested.

Yeah i think you are correct will create a query with Max just to show last entry Thanks always