GB said:
Hi All,
I have a client with a simple requirement for a database. It involves
machinery eg lawnmowers and power tools with a service history. now
the dilemma is because the same piece of equipment is serviced more
than once and also possibly spare parts assigned to it, what would be
the best way to put this into a database without having to re-enter
the same information a lot of times for the same piece if the only
thing that changes in that specific day is a change of oil??
THanks in advance and regards
GB
This is exactly what a relational database system (like Access) is for,
and where it shines over a simple spreadsheet like Excel. With Access,
you define a separate table for each unique type of "entity" -- thing,
person, event -- that needs to be represented, and then you define
relationships between those tables based on key fields they have in
common. These key fields are the only pieces of information that are
duplicated.
For example, you might conceivably define tables like these:
Table: EquipmentTypes
(one record for each type of equipment)
Fields: EQTypeID (primary key),
EQTypeDescription
Table: Equipment
(one record for each piece of equipment)
Fields: EquipmentID (primary key),
EQTypeID (key to EquipmentTypes)
EquipmentDescription
Table: Services
(one record for each type of service)
Fields: ServiceID (primary key)
ServiceDescription
ServiceLaborCharge
Table: Parts
(one record for each part)
Fields: PartID (primary key)
PartName
Manufacturer
PartPurchaseCost
PartSaleCost
Table: ServiceHistory
(one record for each service performed on
a piece of equipment)
Fields: SvcHistID (primary key)
ServiceDate
EquipmentID (key to Equipment)
ServiceID (key to Services)
Table: ServiceParts
(one record for each part used in a
given service)
Fields: SvcPartID (primary key)
ServiceID (key to Services)
PartID (key to Parts)
The above table design is very rudimentary, of course. Anyway, with
Access you set up tables like these to represent the universe of
information that your database is supposed to track, and you define
relationships between the tables. For example, EquipmentTypes has a
one-to-many relationship to Equipment: for each type of equipment there
may be many actual pieces of equipment of that type. Services has a
one-to-many relationship with ServiceHistory, and so does Equipment.
Having defined your tables, you set up forms and subforms to let you
enter or view the data in its proper relationship. For example, you
might have a form based on the Equipment table, with a subform based on
ServiceHistory showing, for each piece of equipment, the services that
have been performed on that piece. When a new service is performed on
some item, you need only open the Equipment form, locate the record for
the item in question, and then enter the details of the service and date
on the service-history subform.
There's a lot that goes into designing a really good, intuitive,
easy-to-use database, but these are, I think, the essentials.
