Please help with a major database undertaking

[Kim NJ]

Hi all.
I started this project with high hopes, but have arrived at a point where I
have built waaaaay too many tables, and can't make my reports do what I need
them to.
Here's what I need:
I am part of a military unit. My section has about 25 pieces of equipment.
Weekly checks are done on all of the equipment. Each item has very specific
things to check, and no two pieces of equipment are alike. I need a db that
will track each item, what was completed on it (in a VERY basic, user-entered
form...not just a text box with writing in it...Can be done with y/n for some
of it), and the calibration/due dates for certain equipment.
I tried one table for each piece of equipment, but am struggling to pull
them together into a weekly report. Not to mention the fact that the DB has
become extremely cumbersome! I need one report monthly, one weekly, all
current calibration and due dates, and I need to adjust the year to the FY
(Oct-Sept).
*sigh*
I've had some very basic training in Access, and I am in WAAAAY over my
head. Does anyone have any ideas? I can figure out how to build the basics,
but it's the fact that each piece of equipment requires different info that
is killing me!!!
HELP!!!

 

[Ken Snell \(MVP\)]

Do you have a table that is a junction (many-to-many) of EquipmentTypeID and
MaintenanceItemID? In other words, a table that has one record for each
pairing of an equipment type value and a maintenance item value? That table
can be used to provide a list of acceptable maintenance types for the
selected equipment (based on that equipment's EquipmentTypeID) and the user
can select the choice from a dropdown box (combo box).

You'd use such a combo box in a subform that holds maintenance actions taken
for a piece of equipment; usually this subform would be in a main form whose
records are the individual pieces of equipment. This subform would be bound
to a table that holds records of which maintenance items have been performed
on each individual equipment:
tblEquipmentMaintenance
EquipMaintID (primary key)
EquipmentID
MaintenanceItemID
MaintenanceDate

Then you can easily write a query to show all the maintenance items
performed on a piece of equipment, using the above table.

 

[Kim NJ]

I think I get it, but I need a confirmation.
When I enter data into this table, here's what I will see:
Equip1 Maint1
Equip1 Maint2
Equip1 Maint3
Equip2 Maint1
Equip2 Maint4
Equip3 Maint5

I need this to fit into a tabbed form or set of nested forms if possible to
track the person entering data and the dates entered/week start date. Can
this be done with this design? Unfortunately, my three guys are all new, and
very inexperienced regarding our equipment. I need the info to be out there
and visible so they can go through and just enter data in provided boxes.
I scrapped the first DB, but the entry part of it was exactly what I want,
and you might be able to glean more info from what I had....as far as what
I'm searching for...
I can email it if it will help anyone help me....

 

[Kim NJ]

After rereading...trying to build the table you're talking about (I think), I
see one potential major issue:
For each type of maintenance, a different input type would be needed. For
example.
Equipment 1: Calibration Date, Due Date, Background Reading, Test Reading
Equipment 2: Sensor1 Reading (x 5 sensors), Calibration 1 performed?
(yes/no), Calibration 2 performed (yes/no), Error reported? (yes/no) ...
errors are tracked in another table for another report.
The form must allow them to enter the individual data according to the data
type required, then update/save the record by date so I can pull up the
weekly report....
AAAAAAAAAAAAHHHHHHHHHH!!!!!

 

[Ken Snell \(MVP\)]

Let's think about your data. If you have many different types of information
that you need to record, and the info is different for each item of
maintenance, then you can quickly "move" to needing some advanced techniques
/ design / programming to make the forms and data entry as easy as possible
for your users. What I list below are some thoughts that may or may not be
within your skill range right now, but I am showing this to prod your
thinking about what the database needs to store and what you want to do with
it.

Please note that this info is "off the top of my head", and most likely can
be modified/improved upon after some thinking and consideration < grin >.

You have various equipment types, such as
Utility Truck Model A500
Jeep Tracker Model VT4
Machine Gun Model 590

Each of the types of equipment would be assigned an EquipmentTypeID --
indicating the type of equipment. So Utility Truck Model A500 might have an
ID value of 1, Jeep Tracker Model VT4 a value of 2, and Machine Gun Model
590 a value of 3. These ID values and equipment descriptions would be stored
in a table named tblEquipmentTypes, for example.

Then you have individual instances of each equipment, perhaps identified by
a serial number or VIN or other unique identifier. These items are stored in
a table named tblEquipment (showing EquipmentID, EquipmentTypeID and
SerialNumber fields, for example)
1 1 9988876
2 1 9834456
3 1 9988998
4 2 A5478J457UYT123
5 2 A509RE457UYT123
6 3 123456
7 3 876543

This tblEquipment lists every individual piece of equipment with a unique ID
value (the first field), and with the EquipmentTypeID (so you know what type
of equipment it is), and a serial number or other identifying
number/characters. You might have other information in this table too if
desired, so long as it's just for that piece of equipment.

Then you would have a table of maintenance items, again with a
MaintenanceItemID value that is unique for each item. This table might be
named tblMaintenanceTypes:
1 Brake calibration
2 Air sensor reading
3 Wheel alignment
4 Windshield replacement

Then you would have another table that lists the items that need to be done
for each equipment item, along with fields to record various data items for
that maintenance item. This table might be called tblEquipmentMaintenance,
and again each record has a unique ID value (call it EquipMaintID):
EquipMaintID
EquipmentID
MaintenanceItemID
MaintData1 (data type may be Number with field size Long Integer)
MaintData2 (data type may be Text)
MaintData3 (data type may be Number with field size Single)
(additional MaintDataX fields)
MaintDatePerformed

and so on. Now, I note right here that the above table violates the basic
normalization rules because of the multiple MaintData fields -- typically,
one would want to put those in a separate table, one record for each
MaintData field value. But, because you say you have many varying data items
that are dependent upon the maintenance item type, this may be the best way
for you to record multiple data values that are different (both in what the
data item is and in what type of data -- text, Long Integer, Single floating
point number, etc.) for each MaintenanceType. So, then, I would add
additional fields to the tblMaintenanceTypes that would hold the captions
for the various MaintDataX fields for that maintenance type:
MaintenanceTypeID
MaintDescription
MaintData1Caption
MaintData2Caption
MaintData3Caption
(additional MaintDataXCaption fields)

And then the sample data might be this:
1 Brake calibration ShoeThickness OdometerReading
CaliperThickness
2 Air sensor reading PercentO2 GaugeColor <
empty >
etc.

You then could use a form that pops up to the user to allow entry of data
specific for each maintenance item, and actually display the captions for
each field to the user for that item, and could hide fields that don't need
to be used for that item. This would require programming in the form to do
this, though.

In a simpler setup, you'd train your users to enter data in the correct
fields for the individual maintenance items for an equipment -- but this
means that you may find many data entry errors. You could use Text format
for all the MaintDataX fields, and then your queries could convert them back
to numbers as needed for calculations, etc.

My intent is to encourage you to give strong thought to the intent and
purpose of the database. Recording maintenance information such as you seek
to do is much more involved than you may have initially considered because
of the complexity of the data you want to store and use.

Give some thought to this and then post back with comments, questions,
ideas, etc.
--

Ken Snell
<MS ACCESS MVP>