Equipment checklist design strategy

[BruceM]

I am putting together a database to track various information about
equipment. Among the information being tracked is an inspection checklist.
The inspection will be performed at specified intervals. I need to save the
information from each inspection. Certain fields are common to all
inspections (InspectedBy, InspectionDate, and so forth). However, the
specific items to inspect are equipment-specific. For instance, one piece
of equipment may have as the inspection items:

ITEM TOLERANCE
Cords
Air pressure gauge ± 5 lbs.
Welds
Deflection 10°

Another piece of equipment may have:

Welds
Bearings

"Welds" is a common item for both pieces of equipment, but other items are
not, although they may be on the checklists for other equipment.

Tolerance information is not required for all items.

I can see that there should be a table for Inspections, related to the
Equipment table, so that each piece of equipment may have many inspections
over time. This would include the fields such as Inspector that are common
to all inspections.

The part I'm tripping over is the line items. The combination of items is
specific to a particular piece of equipment (although other equipment may
happen to have the same set of inspection items). This listing of items
should probably be related to the Equipment information, but unlike the
Inpsection record it consists of several records rather than a collection of
generic fields that need to be filled in. It also needs to be related to
the Inspection record.

I understand perfectly well that data are stored in tables, and that a form
is an interface to the data. However, I am going to describe the hoped-for
result at the form level, in the hope that it illustrates the question about
the underlying structure. I want to navigate to an Equipment record and
enter today's inpsection record. This includes fields such as Inpsector
that are common to all inspections, and a pre-determined list of inspection
items (Welds, Bearings, etc). Each item on that list needs information to
be recorded. Back to the earlier example:
ITEM TOLERANCE OK
Cords Yes
Air pressure gauge ± 5 lbs. Yes
Welds No
Deflection 10° Yes

OK is the only field that receives data during each inspection. The others
are part of the recurring checklist.

 

[Allen Browne]

So you have different types of equipment, and each type needs different
kinds of periodic inspections. We will have to teach Access about that types
of inspection are needed for each type of equipment

Suggested tables:

EquipType: One record for each kind of equipment.

InspectType: One record for each kind of inspection.

EquipTypeInspectType: junction table that tells what types of inspection
apply to what types of equipment, with the tolerance and frequency that
applies to this combination. Fields like this:
EquipTypeID relates to EquipType.EquipTypeID
InspectTypeID relates to InspectType.InspectTypeID
Tolerance Double
ToleranceUnit "lbs" or whatever
Freq Number (long)
FreqPeriod "d", "m", "yyyy", etc.
The Freq and FreqPeriod work together to indicate how often. For example, if
something needs inspecting every 90 days, Freq is 90 and FreqPeriod is "d".
For something else that needs inspecting every 3 calendar months, Freq is 3
and FreqPeriod is "m". (The idea is to use the expression that are valid in
DateAdd(); that way you can look at the last actual inspection date, and
figure out when it's due again.)

Equip: one record for each actual piece of equipment. Fields:
EquipID primary key
EquipTypeID relates to EquipType.EquipTypeID
AcquireDate when you got this piece of equipment
The crucial thing here is that you could have several pieces of equipment of
the same type.

StaffID: one record for each staff member who does inspections.

Inspect: one record for each inspection done. Fields:
InspectID primary key
EquipID what was inspected
InpsectDate when it was inspected
StaffID who did this inspection

InspectDetail: one record for each reading in an inspection. Fields:
InspectDetailID primary key
InspectID which inspection this is a line item for
InspectTypeID what reading this is for
InspectValue what the reading was
IsFailed Integer (yes/no/null)

The InspectDetail table exists because one inspection of a piece of
equipment could result in many readings, i.e. you could inspect several
aspects of a piece of gear in an inspection, so you need a related table
(like invoices and their line items.)

For the interface, you have a main form bound to the Inspect table, with a
subform bound to the InspectDetail table. When someone inspects a piece of
gear, they create the main form record to say who inspected what when. Now
you can use the AfterInsert event procedure of the main form to populate the
subform with a row for each InspectTypeID that applies to the EquipTypeID of
the item in the main form. (This would be a matter of determining the
EquipTypeID from the Equip table, and then executing an Append query
statement for the relevant rows from EquipTypeInspectType.)

Of cause, the InspectValue and IsFailed fields in the subform need to be
blank so the user can fill in the actual values here. In the AfterUpdate
event procedure of InspectValue, you can look up whether that value is in
tolerance or not, and assign the value to IsFailed. The user can then
override the value and determine whether it failed or not, or they can just
enter whether it failed or not for the items where InspectValue does not
apply.

If that all makes sense, here's a final explanation of why I suggested a
Number field of size Integer rather than a Yes/No field for IsFailed (or
IsOk if you prefer.) When the main form's AfterInsert event runs and it
creates the rows in the subform, it doesn't know whether each row passed or
failed, so it must insert a Null. Yes/No fields cannot be Null. Hence you
use an Integer field where:
0 = False
-1 = True
Null = Don't know the outcome yet.
If that's a new concept, I regularly use that approach for yes/no fields
anyway, and there's an explanation of why here:
http://allenbrowne.com/NoYesNo.html

Hope that all makes sense for you.

 

[BruceM]

Thanks for the detailed reply. I have been mulling it over for a while, and
I think I see where some of my reasoning was flawed (for instance, I was
thinking about a single field for Interval), but I still have a few
questions.

I was having trouble with the Invoice Details analogy because an invoice is
created item by item each time. Maybe an invoice and its related records
will be duplicated from an earlier record for the same items, then modified
for quantity, date, and so forth, but in this case a piece of equipment has
a pre-determined list of inspection steps or items. It is analogous to
creating a paper inspection checklist, then making a bunch of copies of the
blank checklist.

I'm still not quite getting how to create the "blank" checklist. I think
can see how to use the Equip table and a related Checklist table (see below)
to create an inspection checklist for each piece of equipment (one that
could be printed out, that is). I can also see how to create an Inspect
table related to the Equip table, so that each piece of equipment can have
many inspections over time, with a related InpsectionDetails table, rather
like the way Invoice Details are related to an Invoice record. However, I'm
can't see how the Append query could be used to bring the Checklist and the
InspectionDetails together when creating a new Inspect record..

Part of the problem may be that I don't understand how the EquipType,
InspectType, and EquipInspectType tables work. I'm clear enough on the use
of a junction table in principle, but I don't see what would be in EquipType
and InspectType. Without getting into a lot of details about the
manufacturing processes, Equipment could be anything from a fixture
including pneumatic clamps and a wide assortment of moving parts to a simple
shield or rack. Also, Equipment is categorized by the manufacturing process
for which it is used, but within a process there is a wide range of
Equipment, some of which is similar to Equipment for other processes. For
complicated reasons, a rack for one process would not be used for another
process. At the same time, the rack for one process may need three
inspection steps, and for another it may need five.

The Equipment is identified by a department code, a process, a sequential
number, and a letter suffix:
R-167-001-A
This information is stored in four fields in such a way that it increments
automatically. Let's say R-167-001 is a rack for the 167 process, and A is
the first rack. Another identical rack would be R-167-001-B. A shield
would be R-167-002-A, B, C, etc. The checklist is the same for all
R-167-001 equipment, regardless of the letter suffix. This may correlate to
EquipType in your example, but I'm just not sure. By the way, the letter
suffix is stored as a number, and is displayed as a letter using Chr. I
have made provisions for AA to be the suffix after Z.

Here's what I have so far:

tblEquip
EquipID
EquipName
AcquireDate
InspectInterval
etc.
Note: InspectInterval has to do with the piece of equipment as a whole
rather than the individual line items.

tblInspect
InpsectID
EquipID
EmployeeID (Inspector)
InspectDate
etc.

tblInspectDetail
DetailID
InspectID
???

tblChecklistItems
ChecklistID
EquipID
Tolerance
etc.

tblEmployee (already exists in another database)
EmployeeID
FirstName
etc.

There are a few other tables such as lookup tables, a Vendor table (from
another database), and a few others that don't enter into the problem at
hand.

To summarize, I have created a way to create a printable checklist, and a
way to create Inspect line items, but I can't see how to marry the two.

I feel like I'm almost getting it, but I can't quite get my mind around the
concept.

 

[Allen Browne]

Explain EquipType:
=============
The idea here is that, over time, you could have several pieces of equipment
of the same type. For example, if you are inspecting microscopes, that's the
equiptype, and if you have 2 actual microscopes to be inspected, they are
the actual equipment. The 2 microscopes might be identical, but inpecting
one does not mean that the other one has been inspected. For the purposes of
this database, anything that has a different set of inspections would need
to be defined as an EquipType. And each individual instance of a piece of
equipment has a record in the Equip table.

In the scenario I suggested, the check list of what has to be inspected is
derived by looking at what kind of equipment it is. So, it it's a
microcsope, then the checklist contains the list of inspections needed. The
check list does not relate to the Equip table: otherwise you would have to
define the check list for every individual piece of gear (for each
microscope individually, intead of for microscopes in general.)

You're closer to your data, so you can make choices here, but you say:
The Equipment is identified by a department code, a process,
a sequential number, and a letter suffix:
R-167-001-A
This information is stored in four fields
Perhaps the EquipType is defined by the department code + the process?

Assign InspectionDetails when Inspection record is created:
===========================================
This kind of thing:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO InspectDetail " & _
"(InspectID, InspectTypeID, InspectValue, IsFailed) " & vbCrLf & _
"SELECT " & Me.InspectID & " AS InspectID, InspectTypeID, Null, Null " &
vbCrLf & _
"FROM EquipTypeInspectType " & vbCrLf & _
"WHERE EquipTypeInspectType.EquipTypeID = " & Me.EquipTypeID & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.Sub1.Requery
End Sub

 

[BruceM]

The EquipType, now that I understand what you mean, is DepartmentCode +
Process + SequentialNumber. The sequential number has no meaning. It is
just one more than the previous high number for that DeptCode + Process.
The four fields (including the letter suffix) are concatenated in a query
that is the record source for various forms and reports. I expect I could
make another query that concatenates just the first three fields, and use
that as the EquipType. Or maybe I need to rethink the numbering system, and
use just the first three fields in my Equip table (EquipType in your
example), with the fourth field in the Equip table.

As I understand it, EquipTypeInspectType is a static listing of inspection
steps (although it could change if the checklist is modified). It could be
used to produce a paper checklist. Inspect and InspectDetails are the
record of the actual inapection, with the InspectDetails corresponding to
the listing in EquipTypeInspectType.

If I could just get a sense of what information you envision in the
InpsectType table I thiink I can sort out most of the rest based on what you
have suggested.

By the way, I see the value of the Integer Yes/No field. I will give it a
try on this database.

 

[Allen Browne]

Yes: EquipTypeInspectType (junction table between equipment types and
inspection types) is a static listing of inspection steps. For each
equipment type, it defines what types of inpsection should occur.

I'm making an assumption here that different kinds of equipment could need
the same kinds of inspection. For exampe, lots of things could need a weld
inspection. Therefore "Weld Inpsection" is a type of inspection (a record in
the InspectType table.) If this is not the case, you don't need this lookup
table.

The immediate benefit of using an Integer field for Yes/No/Null is the
ability to use Null until you know whether it passed or failed. Sounds like
you are on track with that.

All the best.

 

[BruceM]

Ah! I see. I latched onto the idea that InspectType refers to the whole
inspection, not to an individual inspection step, and I couldn't see past
that initial assumption. Your assumption is correct, and the example of
Weld Inspection being used for many types of equipment makes it perfectly
clear how that relationship works.
I think I'm going to break the Equipment table into EquipmentType (DeptCode
& Process & SequenceNumber), with another table for EquipmentTypeSequence
(or something like that) for the letter suffix. I could probably use a
query with the existing table, but I suspect things will change as this
project evolves. The new table will not do any harm, and could be of
benefit depending on how the data need to be sliced and diced. If the main
form is based on a query that includes both tables it will behave just as it
does now with a single table, which is what the user wants to see.
Thanks again for all of your help. This should keep me busy for a while,
along with the rest of the stuff I need to do.