Turning a Record into a Field??

[HW]

I posted this in the wrong area yesterday....

First and foremost, I'm a novice. My ideas of database
design are limited.

I am in charge of tracking several hundred pieces of old
machinery which are scattered around the country. I have
a table that identifies the machinery, serial number,
model,location, and person to contact. This table is
referred to as Machinetbl.

Several times a year the manufacturer or some other
company creates a modification or recall that should be
applied to all the machines. These recalls can come in
three methods. Technical Reports, Safety Messages, or
Modification Instructions. I've created a table for each
of these with a description, date, and what is affected.
These are generally referred to as Messages. Each one has
a unique number and is currently the primary key for each
table. They will never be alike.

I would like to have the database apply all three types of
messages(totaling about 200 in archived records)to all the
machines and have a check box indicate if it was complied
with a particular serial numbered machine.

Currently, I believe I need a way to add a record to one
of the message tables and have it automatically add a
field with the message number to the machine info table.
Is this possible? Is there a better way to tackle this?
If so, how would I force the newly generated field to
default to a check box (Define the properties of the newly
generated field).

My VB is marginal at best. I can barely spell it
correctly, but I'm willing to learn.

I have a fancy book on Access (Access 2002 Inside Out),
but haven't found what I'm looking to do.

Any help would be greatly appreicated. Thanks!



..

 

[Tim Ferguson]

I am in charge of tracking several hundred pieces of old
machinery which are scattered around the country. I have
a table that identifies the machinery, serial number,
model,location, and person to contact. This table is
referred to as Machinetbl.

Hmmmm: from what follows I would suggest tables as follows:

Machines (*MachineNumber, Serial, Model, IsCurrentlyAt)

Locations (*LocCode, PlaceName, AccessType, PersonToContact)

People (*PersonID, FullName, PhoneNumber, EmergencyPhoneNumber)

Note that Machines.IsCurrentlyAt is a foreign key that points at
Locations.LocCode; and Locations.PersonToContact is a FK that points at
People.PersonID. This assumes that a machine can only be at one location at
a time (or do you need historic information about where they _were_
located), and that there is one person for each location: but these
assumptions may well be wrong!

Several times a year the manufacturer or some other
company creates a modification or recall that should be
applied to all the machines. These recalls can come in
three methods. Technical Reports, Safety Messages, or
Modification Instructions. I've created a table for each
of these with a description, date, and what is affected.
These are generally referred to as Messages.

In that case I would probably put them all in a Messages table with a field
called MessageType to separate them. If a Message applies to more than one
Machine, you should have a table called AppliesTo that looks like this:

AppliesTo (*MessageNumber, *MachineNumber, HasBeenSeenTo)

that links each message to the machines. This is a classic many-to-many
relationship. You would generate the records by an append query that puts
in the correct MachineNumbers according to the Make and Model or whatever.

have a check box indicate if it was complied
with a particular serial numbered machine.

You could set AppliesTo.HasBeenSeenTo to true when the recall has been seen
to, for example.

My VB is marginal at best.

Well, you are still at the DB Design stage, and the VB stuff only comes in
when you get to the user interface -- WAY down the road! You may well not
need any VBA in any case.

Hope that helps


Tim F