MS Access: Delete query to limit the number of records in a table to the most recent 10,000

Question

I am using MS Access 365. I have a table containing records that is updated throughout the day. Since this table can grow large, I want to limit the number of records in this table to the most recent 10,000 (recent is defined by a Timestamp field). What combination of TOP LIMIT and SORT BY by can I use to create a query that deletes the oldest N-10000 records

Thank you

Answer 1

I assume you have an ID field in your table which I named tblData then you could use the following SELECT statement to display all records which should be deleted

SELECT * FROM tblData
WHERE ID NOT IN 
(SELECT TOP 10000 tblData.ID
FROM tblData
ORDER BY tblData.dateStamp DESC);

In order to delete the data you only need to replace the first SELECTwith a DELETE