Equipment Maintenance Database

[Richard Connell]

I am trying to design a database which will be used by Technicians and
Engineers to store information relating to lots of different pieces of
equipment in a manufacturing plant. I have the basics of a startup form which
hyperlinks via buttons to a large amount of forms which store the info to
individual tables for each piece of equipment.

1 - Is there somewhere that I can read up on all of this to make sure I am
not making a mess of it from day one.

AND

2 - How can I write a query which will allow someone from management to
print out a report of all work carried out in any given specified 7 day
period. I should tell you that every entry has it's own date and time stamp
BUT I cannot figure out an easy way to set up a user friendly form/query
which is friendly to the less technically minded people who may need to use
it.

Any help

RC

 

[Allen Browne]

Hi Richard

This is a fairly involved task. To achieve it, you will need to be
comfortable with database design concepts (normalization), SQL (query
statements), and VBA (writing Visual Basic code).

Typically, you need tables for:
- General Category of equipment (e.g. a printing press);
- Specific type of equipment (e.g. a Fregenback model 200)
- Machine Instance (e.g. serial number 99987)
- General Maintenance type (e.g. Service B)
- Specific Maintenance item (e.g. replace rollers)
- Maintenance Detail (General contains specific)
- Service (on record each time a machine instance is serviced)
- ServiceDetail (items covered in a service)
- ServiceMeetsSpecificMaintenance (the maintenance items covered in a
ServiceDetail)

The specific maintenance items will have a frequency associate with it, e.g.
it may need doing every 10 days, or every 3 months, This table will
therefore contain fields:
PeriodType (e.g. "d", "m", "q", "yyyy")
PerdiodFreq number, = time between services.

To calculate when a service needs to be done again, it is then:
DateAdd([PeriodFreq], [PeriodType], [last date of service])
where the last date of service is calculated as the Max of the
Service.ServiceDate where the service met the specific requirements, or if
that is null (i.e. it's never been servcied) the acquisition date of the
machine instance.

It starts to get more involved if some equipment needs to be serviced based
on the number of running hours (or miles for vehiciles) instead of fixed
periods. In this case, you must ensure that the hourmeter/odometer is
entered with each service, and over time you can build up a
typical-recent-usage pattern to predict when the service is likely to be
needed.

Additionally, you may need to handle services that contain other services
(as happens with vehicles), so that if it's had an A service that also
satisfies all the items in the B service.

Then you have the fuzzy logic associated with combining elements into a
service, e.g. you don't want to replace a drive belt today and then have
your software tell you that it needs oiling tomorrow when it would have been
easier to do that today.

Hope that's of some benefit to help you think through the aspects of the
task.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Bruce]

Allen Browne has posted here often, and knows far more about Access than do
I. However, I will go ahead and offer a few thoughts. Your design choices
will be guided by your specific needs. For instance, a single equipment
table may be the away to go, rather than having a general category table at
the top of the structure. Categories can get tricky when you need to decide,
for instance, if a pneumatic drill is a drill or a pneumatic tool. Unless
there is a very large amount of equipment a single table could work well for
both general category and machine type. Similarly, it may make sense to keep
serial number and maintenance frequency in a single table. Or it may not.
All I'm saying is that you have to make choices, and you need to do so first
at the table level. That is where the relationships are established.
For setting query (or report) criteria such as date range, see Help on the
subject, or search newsgroup postings. Setting a date range is not
difficult, and is well-explained elsewhere, so I will not go into details
here.
In thinking about tables I have found a good general rule is that you should
be able to describe the tables's function in a single sentence without using
the word "and". For instance, Machine Details (serial number, purchase date,
and any other information that is specific to that machine) makes sense for a
table; Machine Details and Maintenance History does not. Maintenance History
is a separate table, linked to the Machine Details table. Remember that it
is easy to add records to a table, but adding fields can present
complications. If you use a General Maintenace Type table, you probably want
a linked table for Maintenance Type Details, so that if you need to add an
item you can do so without difficulty.
This is a good project for using a relational database's capabilities, but
as Allen has pointed out, there is a lot to consider. You mention that you
have forms that link (hyperlink?) to other forms, but table structure,
relationships, and database design are where you need to begin. Forms come
after the structure is built.
For books, try a google Groups search for Microsoft Access Books (or
something like that), and see what has been recommended.