Table Design Help

[jjake]

Hello. So i am having difficulty making a table design for a work order form.

On my form i have a combo box, cboEquipmentID. This is where the user will select the piece of equipment that the work order will pertain too. My equipment could be anything. all equipment is stored in tblEquipment

e.g

Car
Forklift
Tractor
Office
Basement
Pump
Boiler
Compressor
(40+ More peices of equipment)

What i have done. I have divided each piece of equipment up into a category in a new table tblEquipmentType to break them down a little bit.

e.g

Vehicles - Car, Forklift, Tractor
Building or Structures - Office, Basement
Production Equipment - Pump, Boiler, Compressor

On the form i have a box where i select my equipment type which then filters the equipment that matches it for me to pick from. This works great!.

My problem :banghead:

I store details for every piece of equipment.

e.g

Manufacturer
Model #
Serial #
HP
Lift Height
Vin Number
Building Location
Pump Size
Pump GPM
Boiler Board #
(150+ More fields that petain to different equipment)

Would i store all off this detailed information in the same table as all the equipment even though more then half of them are not used for each other?

I tried a different table tblEquipmentDetails but when it comes to entering new information with a form the record does not yet exist in the details table
so i cannot filter to find it to add to it, after i entered it into tblEquipment.

Thanks.

 

[jdraw]

jjake,

Can you describe to readers what the business processes related to this info would be? Simple, plain English.
That is, what occurs in the business, and what information do you want to record?
And, what do you do with the information collected/stored?

You may get some design ideas from this free video by Mark Serva sub/supertypes

Good luck with your project.