Question Inventory management database (1 Viewer)

[g.walker]

Hi,

I'm using Microsoft's template "Inventory management database", unsurprisingly there are lots of custom changes I'd like to make.

One of these is modifying it to allow me to generate sales orders, allow me to sell bundles or kits built up of multiple parts and in different quantities.

The way I see it I would have the following tables:
Products - to include details of the product or bundle.
Product-Parts - to include what parts from the parts table would make up the overall product bundle.

I'm stuck with how to register these type of sales on the transaction table so the inventory will record the multiple items.

Any help would be greatly appreciated.
Thanks,
G

 

[jzwp22]

Welcome to the forum!

If you treat a product as just another part, things become simplier.

tblParts
-pkPartID primary key, autonumber
-PartNo
-txtPartName

You would then use a table similar to this to relate a "product" part with the subparts used to make it:

tblPartAssemblies
-pkPartAssemblyID primary key, autonumber
-fkPPartID foreign key to tblParts (represents the "product" part)
-fkSPartID foreign key to tblParts (represents the subpart)
-QTYsPart (quantity of the subpart that is used to make the "product" part)