Preventative maintenance program

[bridgeman]

I was wondering if anyone has any experience using access to manage a
preventative maintenance program. The maintenance activities would be
recurring depending upon the frequency in terms of the weeks. I have created
a table thus far that contains all 114 of maintenance activities, using the
following fields.

txtActCode - "represents a code for the activity"
txtActDesc - "brief description of the maintenance req'd"
txtActType - "either "M" for mechanical or "E" for Electrical'
txtEquip - "represents the equip needed for the work"
numFreq - "can be and digit like 1,2,...etc representing the frequency of
the event in terms of weeks"

The below are fields in the table that represent the structures in which
these PMs occur. I used check boxes to indicate which activities are relevant
to each structure.

chbMainSt
chbOrtega
chbSisters
chbCrescent
chbBOL
The following are the requirements of the DB.
1.) Generate a PM schedule for the upcoming week, or any week in the future,
or any week in the past. The list will be grouped by structure number and
include PMs for the period of one week.
2.) Also if for instance as an example if a pm that has been assigned for
the week does not get completed that a report of uncompleted PM can be
generated.
3.) Another table might be used to track the history of the PM to include
the date completed and the person completing the PM.

In advance I appreciate any help or advice.
Cordially,
bridgeman

 

[Arvin Meyer [MVP]]

STOP!

You are creating a spreadsheet, not a database. All structures should be
records in the table, not fields. It seems that you did it correctly for
maintenance activities. Now what you need to do after fixing the structures
problem, is to create another table with a row for each structure and
activity.

 

[bridgeman]

Okay I have done that. Do you recommend possibly creating a table for
anything else contained in the table? I am going to make the primary key the
txtActCode, and for the recently created structure tables I will include a
field for the structure number. I was thinking that I may want to create a
separate table with the txtActCode and numFreq fields and use that as an
index.

 

[Steve]

Hello,

I would lile to offer to create the PM database for you. I provide help with
Access, Excel and Word applications for a very reasonable fee. Let me help
you and you could have your database up and running in a short time. Contact
me and let's work together on the database.

Steve
(e-mail address removed)

 

[John... Visio MVP]

I would lile to offer to create the PM database for you. I provide help
with Access, Excel and Word applications for a very reasonable fee. Let me
help you and you could have your database up and running in a short time.
Contact me and let's work together on the database.

Steve
(e-mail address removed)

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP

 

[Arvin Meyer [MVP]]

Unless there is a universally agreed upon ActCode, that even a new hire
might recognize as a mistake, I suggest using an autonumber as the primary
key.

I'd also use an autonumber for the structure number.

As far as the frequency goes, is it an attribute of the Maintenance Act or
the Structure? IOW, can there be a different frequency of the same
maintenance for different structures? Most likely, the answer is yes and so
the frequency for each maintenance act needs to be in the third table used
to join maintenance and structures.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

This is a free service provided by volunteers, not those looking to make a
quick buck. Please peddle your wares elsewhere.

 

[KARL DEWEY]

Here are some thoughts - but first do not response to Steve as he is trolling
for business that others say is poor quality in a forum that provides FREE
assistance.

Create a Periodic Maintenance (PM) table that list all the PM services you
will perform. In this table use the lowest common denominator of your
intervals. Most folks do not call a daily a PM but have weekly as the lowest
interval. If weekly is your lowest then compute all services in multiples of
weeks - a 2-month would be 9 weeks, 6 months would be 26 weeks.

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.

Decide when the next PM will be scheduled based on last schedule or last
completed. 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.

Build a junction table of all equipment, services (PMs), Last Schedule, Last
PM Complete, and flag for last schedule or last completed.

Run update query each time a PM work order is closed - flagged closed.

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.

Create a Work Order table that has Equipment, PM, Schedule Date, Completed
Date, Maintainer, Comments (to be able to record exceptions and problems),
Closed, and other fields as needed.

Generate PM work orders each week for the next however many weeks you plan
in advance. Use an append query to generate the PM work orders.

 

[bridgeman]

I believe that I am following what you are saying but In both cases using an
autonumber might not work. There cannot be a different frequency of the same
actCode regardless of the structure on which it is being executed. I did
create a separate table for the frequency and used the actCode as the primary
code. I can provide you a sample of what I am doing if you feel it would
help. If you would like one let me know and how I can deliver it. I will
leave the data in there.
Cordially,

 

[Dirk Goldgar]

[...]

Arno R

Arno, please change the false e-mail address you are posting with. The
language violates the newsgroup guidelines.

Thanks.

 

[Dirk Goldgar]

What is this Dirk ??
I have posted lots and lots of messages here with
StopThisAdvertising@[...]
Now I changed it to Stop$teve@[...] since he is the ONLY one advertising
here...
What's the difference ??

No difference -- I just hadn't noticed the "domain" before. Anyway,
Microsoft does ask newsgroup users not to use profanity, and I agree with
that policy. It makes the newsgroups a more civil environment, even for
those who aren't especially shocked by foul language.

Besides: Why aren' t you telling $teve that he violates the newsgroup
guidelines ??

I don't have to; you and John are already doing a bangup job at that -- not
that it seems to be discouraging Steve, I'm sorry to say. I've told him the
same in the past, for all the good it has done.

 

[Arvin Meyer [MVP]]

The primary key need not be seen by the user. As a matter of fact, it's a
good thing if they don't see it. You can still have an actCode, if you like.
What I was trying to point out is that the primary key, is important to the
data relationships, not to anything meaningful. In a few instances, a
natural key makes sense, like a StateAbbreviation. It will never change and
it is universally known.

In a database foreign table, the relationship is maintained by the key, and
only the key. If a mistake is made in a main table, like hitting CS, instead
of CA, it is easy to find and fix. Everyone knows that there isn't a state
CS. But an arbitrary key which has meaning can easily slip by unnoticed and
have grave consequences, depending upon the data usage.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com