I have a database I set up for a department to keep track of charts they send out, where they go, and when they're returned. There is getting to be a LOT of records in the database, so I'm wanting to set up some kind of purge function for them.

I know I can set up a delete query but I have a couple questions. The date we'll go from is called returned_date, and they want to delete records that were returned 6 months ago or longer, and keep all records returned within the last 6 months. I'm not sure how to do this, other than having them manually calculating the date 6 months ago, and prompting them to enter it when running the query. Is there an easier way?

Also they want to view the reports before deleting. I was thinking of setting up 2 separate queries that are exactly the same.....only one will be a delete query and the other one won't. Then I can just make a report based on that query. Is that the best way?

Thanks in advance!

Create two queries (type each in a new query's SQL View):-

qryViewOldRecords:-
SELECT *
FROM yourTable
WHERE Returned_date < DateAdd('m',-6,Date());


qryDeleteOldRecords:-
DELETE *
FROM yourTable
WHERE Returned_date < DateAdd('m',-6,Date());


Run the first query to view the old records. Produce your report based on this query.

Run the second query to delete the old records.

Thanks for your reply.

I did what you suggested, but when running the query, I am prompted for a return date. I'm assuming that's not what you intended...?

Never mind, it worked. I forgot to change my field name from what you had to what it was actually called (ReturnDate)

Thank you!!!