If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Spare parts "used on" code
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? |
#2
|
|||
|
|||
Spare parts "used on" code
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. "Mark S" wrote: 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? |
#3
|
|||
|
|||
Spare parts "used on" code
We're doing what you describe. The structure that Doctor described is what
you need. |
Thread Tools | |
Display Modes | |
|
|