Spare parts "used on" code

[Mark S]

I have an existing inventory database that is used for spare parts for other
equipment. There are currently about 7000 spare parts for about 50 different
pieces of equipment. The maximum forseeable growth would be to under 100
pieces of equipment.

I would like to add a "used on" code to the database to better track what
spares are used on which piece of equipment. This would primarily be used
when a piece of equipment is sold or becomes obsolete, the spares could go
out the door with it. Unless of course the spare part is used on another
piece of equipment.

Would it be better to use an individual logical field for each piece of
equipment? Access allows up to 255 fields, so even at 100 fields, the spares
inventory table would be well under that size.

Or should I look to somehow encode and decode a single field with the
information I need? If so, any suggestions?

 

[Doctor]

Mark, if I understand you right, I think what you need to setup is a
many-to-many relationship between your equipment table and your parts table.
To do this you actually setup a third table with the following definition.

tblEquipmentPartsLink
EquipmentID (set the field to number, required, indexed (duplicates ok))
PartID (set the field to number, required, indexed (duplicates ok))

When in table design, select both EquipmentID and PartID in your new table
and then click the Primary Key buttong. This will create a primary key that
is based on both fields.

Then in the Relationships window, create a relationship between your
Equipment table and the EquipmentID in your new table and your Parts table
and the PartID in your new table. Access will recognize this as a
many-to-many relationship between your two tables. You will now be able to
store many parts with many pieces of equipment.

In access 2007 you could build this type of relationship with only two
tables by setting the Allow Multiple Values to Yes in table design view, but
even still I would do it the way above if I plan to have more than 4 or 5
records related.

Hope it helps.

 

[Fred]

We're doing what you describe. The structure that Doctor described is what
you need.