Can I create an assembly in Access?

[Lilshulf]

I'm creating an inventory database and want to combine items into assemblies
ie batter helmet, face mask, chin strap together are helmet assembly. The
helmet assemblies would be placed in team bags but each individual part may
have to be replaced at different times.

 

[Lynn Trapp]

You will need to add a ParentID field to your inventory table. Each item in
the assembly would have the InventoryID (or whatever your primary key is
called) of the assembly in the ParentID field. You would have to have a
record in the inventory table for the full assembly.

InventoryID Description ParentID
101 Helmet Assemblly
102 Batter Helmet 101
103 Face mask 101
104 Chin strap 101

It can get a lot more complicated than that, especially if a particular part
could be used in multiple assemblies, but this should get you started.

 

[Lilshulf]

Thanks
Can you explain how to handle it when a part (chin strap) is used in two
different assemblies? We have two helmet manufacturers. They make their own
face guards but the chin straps are universal.

 

[Lynn Trapp]

One way to do it is to create a "phantom" record that merely serves as a
holding record for the steps in the final assembly.

InventoryID Description ParentID
101 Final Helmet Assembly
102 Batter Helmet 1 105
103 Face mask 1 105
104 Chin strap 101
105 Helmet Assembly PH1 101
106 Batter Helmet 2 108
107 Face Mask 2 108
108 Helmet Assembly PH2 101

Then when you go to put together a Helmet assembly you have to choose the
appropriate phantom record, which consists of the appropriate helmet and
mask. The chin strap is, in this case, always included in the Final
assembly. Obviously, phantoms can go down to multiple levels.