delete query (1 Viewer)

[lipin]

I am storing employee records in a table. The information includes incidents they were invloved in. Three in a year = suspension or termination. I am looking for a way for the incident to delete after 1 year so it is no longer on the employee's record. Is there a simple way to automate this process?
I was thinking of a delete query but have no idea how to set it up to run.???

Any ideas on how to accomplish this are greatly appreciated. Thank You.

 

[TessB]

Lipin,

Do you mean that you have the delete query designed but need to figure a way to run it periodically to clean out the expired (older than a year) data?

Or do you need help on the design of the delete query itself as well?

Tess

 

[The_Doc_Man]

Well, maybe...

There is a way to do this using a macro, a query, and the Windows Scheduler

Make a query to do the delete. It can be something so simple as

DELETE * FROM EMP_EVENTS WHERE DateDiff( "d", [EventDate], Now() ) > 365 ;

(Make sure you put "Now()" as the second date argument and the date from the record as the first date argument. The "d" specifies units of days. The answer will be a long integer.

Then make a macro that does a RunQuery of this Delete query followed by a Quit. You might wish to include a SetWarnings False just in case something goes wrong.

Then use the Windows task scheduler to run a command-line for Access on that database using the "/X macro-name" option as part of the command line.

See help topic "Startup Command-line Options" for more on this approach.