Looking for a simple solution

[R Whittet]

Hello. The company I work for has been using Microsoft Access to keep track
of our maintenance records for some time now. I have been given the task of
setting up a preventative maintenance schedule for years to come and I am
seeking advice. What I have are 3 tables. One, is the equipment list,
another for work tasks, and the last for scheduling type. Some of our
maintenance items are performed more frequently than others. This is why I
have the scheduling type table (monthly, quarterly, semi-annually, and
annually)

What I would like is to assign a certain task (say it's the quarterly type)
to a month such as January. From there I would like the task to be repeated
on my report every third month, so that when the time comes, the maintenance
is performed. I cannot find a way to do this except by telling the database
every month I want each task to show up. This is not a small maintenance
program. There are roughly 1,000 jobs to sort through. Making this program
work as easily as possible is my goal. Thank you in advance for your input.

Sincerely,

Rick

 

[Klatuu]

Use the Scheduling type (Interval) and the Last date the maintenance was
performed to tell you when the next due date is:
dtmDateDue = DateAdd("m", 1, [last_performed_date]) - Monthly
dtmDateDue = DateAdd("q", 1, [last_performed_date]) - Quarterly
dtmDateDue = DateAdd("q", 2, [last_performed_date]) - Semiannual
dtmDateDue = DateAdd("yyyy", 1, [last_performed_date]) - Annual

 

[KARL DEWEY]

This is why I have the scheduling type table (monthly, quarterly,
semi-annually, and annually)
I do not see the need for Scheduling Type table. In your Task table have a
field indicating interval number for the maintenance. Use the lowest common
denominator such as weeks, months or quarters.
If you can not make it work with the lowest common denominator then use two
fields, one for interval type and other for numerial --
m 2 - for 2 months
d 30 - for 30 days
q 2 - for 2 quarters
Use these in DateAdd function to create your workorders using an append query.

Another thing to think about is whether to schedule based on last performed
date or straight calendar. If a maintenance task was performed late or
earlier should the next one be be form the completion date or whenever the
calendar says it should be. Have a field in the task table indicating which
if you have mixed.
The workorder needs a date field for DueDate and Completed. The append
query will look at task table for interval information and which date to use
- last completed or last scheduled.

 

[R Whittet]

Thanks so much for your input. This is working great!