Reoccurring events

[dhborchardt]

What is the best way to handle events that are going to come up at sheduled times. For example I have a bunch of equipment that will need maintenance at different times throughout the year. An air handleing unit is going to have its filters swaped 3 times a year. When I entered this piece of eqipment into the database, the date field was set to now and the reoccurrance was set to 4 months. I want to be able to run a query that will show everything that has come up for maintenace and once I put a check in the finished box it will not come up again until 4 months have passed.
Thanks

 

[rac2]

Seems pretty straightforward as you describe the requirement. I think a table for MaintenanceTasks with columns to describe the task plus a DateDue column for the next scheduled maintenance, and an integer valued column CycleMonths. Submitting the form would lead to UPDATEing the rows for the tasks with checkmarks to a DateDue of Now() plus CycleMonths months.

If you have recurring schedules measured in days or years as well as months, you might have a couple of columns CycleUnit and CyclePeriod.

If you want to keep a history of completed maintenance you could add a second table MaintenanceHistory. Submitting the form would INSERT rows with the date today and a key to identify the task.