Maintenance PM's

[koncreat]

I am creating a database for our maintenance department to use for preventive maintenance on manufacturing equipment. We have PM’s that have to be completed at different intervals. We have monthly, quarterly, bi-annual and annual. So once a PM is completed the next time it will be done again will be according to the how often it is done and the last completion date.

They are asking for these to show up on screen when they are needed to be done again. It has several machines and PM procedures so I am thinking of creating a table for each machine that would contain the PM that machine will get and a date field that would contain the last completion date.

What I a having a hard time with is 1) so if a machine has a PM, how frequent doesn’t matter, how do I get it to show up in the form? 2) Once the PM is complete and they enter the data in the database I need to update the machines table for that PM with the completion date and also keep the data that was entered for history

 

[theDBguy]

Hi. I would probably start out with something like this:

tblEquipment
EquipmentID
etc.

tblPMs
PMID
Interval

tblEquipmentPMs
EquipPMID
EquipmentID
PMID

tblCompletedPMs
CompID
EquipmentID
PMID
DateCompleted
etc.

Hope that helps...

 

[Deleted member 147267]

I am thinking of creating a table for each machine

Definitely not. This is something that I have a lot of past experience with but would have to reflect on what the table schema was. If you are willing to do a bit of setup work you can make this work very well.
You'd need one table for equipment, one for PM types. A lube done at 3 months and the same lube done at 6 month intervals (because one machine needs one and another class of machine needs the other) is 2 PM types, not 1. The rest of the details depends on what else supports the execution of these pm's. Is it a work order based system? That would be beneficial to the goal because you'd manage all the planning and completion data via work orders. If not, you'd need junction tables for machine/pm history.

EDIT - too slow I guess.

 

[Deleted member 147267]

cross posted
https://www.accessforums.net/showthread.php?t=81008

 

[jdraw]

Review the Similar Threads at the bottom of this page.
And/or
See this generic model for Equip maintenance at Barry Williams' site.

 

[Pat Hartman]

I would not call the final table "completed". Maybe "scheduled" would be more appropriate.

tblPMs defines the universe of PM tasks and the interval at which they need to be performed.
tblEquipmentPMs defines the PMs that this particular piece of equipment needs but says nothing about schedule or completed.
tblPMSchedule defines the PMs that are scheduled and includes scheduled date. Once CompletedDate and possibly Results (should this be an inspection task) are filled in then the task is complete.

To get records in the tblPMSchedule, is done in two places.
1. When a new tblEquipmentPM is added, the first scheduled date should be included so that a scheduled record can be inserted into tblPMSchedule.
2. The form where tblPMSchedule is modified to record completedDate and results should use the data in tblPMs to calculate the next scheduled date and insert a new record into tblPMSchedule

This can be a calculated process and your business rules will dictate how the PMs are controlled. For example, does the world come to an end if a PM doesn't happen on the day it is scheduled? Should the machine be declared "unusable" until the PM has been completed? How do you want to notify the staff of pending PMs so they can get them on their schedules. How do you want to notify the staff of late PMs? Lots of things to think about.

Having managed a multi-million dollar mainframe project to design and create an application to do plant maintenance, I also have quite a bit of experience in this area.

I don't like any of the schemas I've ever looked at on the Williams site so I don't recommend them.

If you want to post your schema, we can take a look and make suggestions before you get too much further into the design. Getting the schema right at the beginning is critical.