Looping Query or Constantly running Query in Access

[Matt Lawrence]

Would it be possible to set up a Query that constantly runs to check for the year? Then when the year changes it would delete data for the previous year.

The big thing is that I want it to keep running and checking.

Thanks

[Stewart Ross]

Access queries cannot be run in the way you ask (asynchronously on a constant basis monitoring for a state change) - but then there is no need to do so. You are only looking for a change of year, so why not set up something which does so when the database is started? If you have a default form that is opened at startup you could use the form's On Load event to run some kind of check and take whatever action is necessary.

I would be cautious about deleting data from the previous year, however. You don't tell us anything about what you are storing or why you need to delete data at end-of-year.

Unless you are dealing with vast amounts of data, don't. Relational databases very rarely require this kind of data removal - for the very good reason that the data stored is a history of the transactions conducted for or about the entities you have stored in the database, and to delete data arbitrarily is to lose information which would be better kept, in my opinion.

-Stewart

[Matt Lawrence]

Thanks for the reply that was great help. The data I am using is simply a incrementing value linked in with another piece of software. so when a new order is put in it will update. This is so we can see how many orders are put through a year.

[NeoPa]

Instead of clearing it all down and starting again from scratch you could include the year value in the ID and simply reset the count whenever the year changes (Using Year(Date()) to determine the current year and checking only those existing records which match that year).

[Rabbit]

How are you going to see how many orders are put through a year if you're deleting all the data each year?

And if all you need is to see how many orders are put through in a year, don't you have an order date field you can use for that?