How to delete extra rows in a table?

[nano2k]

Hi

In an MS Access database, I have a table that keeps the history of
some user actions.
I need to implement a mechanism to keep the number of entries in this
table to a decent value.
Shortly, I want to keep only the newest 5000 records. Each record has
a timestamp field.
I there a query that will delete all other records?

Thanks.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

DELETE *
FROM table_name
WHERE date_column <= #earliest date you want to keep#

Example of WHERE clause:

WHERE invoice_date <= #2/1/2008#

Substitute your table and column names.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR6wNxIechKqOuFEgEQKqXQCeM589Ts1t7cWhQ9iWwXmE3iCk9U0Anj+c
057CrebvnUNbCVp6lLAZt1se
=FLIs
-----END PGP SIGNATURE-----

 

[John Spencer]

DELETE YourTable.*
FROM YourTable
WHERE DateTimeStamp NOT IN
(SELECT TOP 5000 DateTimeStamp
FROM YourTable
ORDER BY DateTimeStamp DESC)

My question is why do you think you need to delete these records. 5000
records is trivial. Access will support hundreds of thousands of records
and with proper indexing the speed of locating the correct records is very
fast.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..