Purge all but last 100 recs

U

UpRider

How would I purge all records in a table except for the last 100 recs added?
TIA, UpRider
 
E

Emilia Maxim

---------- "UpRider said:
How would I purge all records in a table except for the last 100 recs added?
TIA, UpRider
Click to expand...

What do you mean with "100 last recs"? The newest 100 when sorted by
creation date+time? Is there a field containing the date +time of
creation? Or do you have an autonumber primary key?

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
U

UpRider

Yes, there is an autonumber. I also notice that when I view the table
contents, records are always added at the end of the table, thus the last
100 are the last 100 added.
In addition, one of the fields is a date/timestamp. I would define the last
100 as the most recent timestamps.
UpRider
 
Y

yann

You can use sql statement

DELETE * FROM <table_name> WHERE <AUTONUMBER_Col> NOT IN (SELECT TOP 100(<AUTONUMBER_Col>) FROM <table_name> ORDER BY <AUTONUMBER_Col> DESC)

replace <table_name> with the name of the table with your records and
<AUTONUMBER_Col> with the field name of the autonumber field.

Hope this helps.

regards,
yann
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OpenRecordset SQL using wrong SQL statement 7
Linked Files 3
Cannot find a record in table after SQL insert error 0
Weigh tasks 0
Seek Method for Multiple-field index 2
Auto Numbering...Default Value: =DMax("[Task#]","[QryTask#]")+1 4
Access Report - Field to toal Group Totals 1
Greyed out button if record =0 1

Top