Keep only some records

  • Thread starter Thread starter Laura
  • Start date Start date
L

Laura

I have a table with an AutoNumber key. I add records with
a certain frequence. I only want 500 records in the table,
so every now and then I want to clean up all records older
than my 500 latest.

How is that done?

Like sort descending on autonumber and then delete all
after the 500th record.
 
Laura,

Assuming a table named MyTable, with an autonumber field named ID, here's an
example query that will delete the excess records:

DELETE MyTable.* FROM MyTable
WHERE MyTable.ID Not In (SELECT TOP 500 ID FROM MYTABLE ORDER BY ID DESC;

Change the table and field names to the actual ones, and paste the
expression in an SQL query window (New query in design view, add no table,
switch to SQL view).

You will neet to run this periodically - right after each batch of new
records is added would be a good time to do it.

HTH,
Nikos
 
Back
Top