Alec said:
I work in a place with 20 pieces of machinery. We inspect them all weekly,
and fix them when necessary.
I am trying to make an Access database that lets me: log the results of the
weekly inspections of all machines in a single form; log work done on
individual machines; print a report of the results of the weekly inspection
of all the machines; print a report of the maintenance history of any
individual machine.
But I just can't seem to work out what tables I need.
It's making me tense, and keeping me awake at night!
Hi Alec,
Ask yourself, what are my entities (real-life persons, places, things or
events) and their attributes (categories of information relevent to each
entity)?
Entities = tables
Attributes = fields
At first glance, you have Machines, Inspections, and Repairs as
entities. Do you have a maintenance staff, and do you plan on keep track
on who inpected and/or worked on a machine? Then you probably need a
People table, too. Need to keep track of specific actions performed for
a particular repair (e.g.part replacement, bolt tightening, etc.)? In
that case you might want a RepairTasks table, as well.
Once you have determined what tables and fields your db needs, then set
your primary keys (using one or more fields) to uniquely identify each
record in a table.
After your pk's are in place, you have to tell Access how to bring all
the information together again. Ask yourself, How are my entities
related to each other? Can each person inspect one OR MORE machines? Can
each machine be inspected by one OR MORE person or persons? Can each
machine be inspected one OR MORE times? Can each machine be repaired one
OR MORE times? Can a repair consist of one OR MORE tasks? Can a given
task be performed for one OR MORE rpairs? On one OR MORE machines?
To set up relationships between tables, you add one table's pk to the
other table (in which case it's known as a foreign key). To decide which
table's pk should go where, you have to figure out how entities are
related to each other:
- In a one-to-many relationship (1:m), a record in Table A can have more
than one matching record in Table B, but a record in Table B has no more
than one matching record in table A. To set up this relationship, add
the field(s) that make up the pk on the "one" side of the relationship
to the table on the "many" side. Check out the Northwinds example
database included with most versions of Access for an example of such a
relationship between the Suppliers and Products tables; one supplier can
supply more than one product, but each product has only one supplier.
- In a many-to-many (m:n) relationship, Table A can have more than one
matching record in Table B AND tblB can have more than one matching
record in tblA. This type of relationship cannot be specified to Access
as such; you have to break up a m:n relation into TWO 1:m relationships
by using a junction or "resolver" table. You put the pk's from each of
the two tables into the resolver table, which then acts as sort of a
traffic cop between the other two tables. Again, refer to the Northwinds
db for an example...the OrderDetails table resolves a m:n relationship
between the Orders and Products tables (each Order can include one OR
MORE Products, AND each Product can be ordered one OR MORE times).
- One-to-one (1:1) relationships are rare, and generally (but not
always) mean you need to rethink your table design. In this relationship
type, tblA can have only one matching record in tblB, and tblB can have
no more than one matching record in tblA. If entities are related 1:1,
that usually (but not always) means their attributes can be combined in
a single table.
This MS support site contains links to many excellent resources on
relational database design:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;289533
Hope this helps. Good luck!
LeAnne
