ID / Description
1. Monitor
2. Mouse
3. Keyboard
4. CPU
5. PC (Item 1 - 4)

Is there any way to cut 1 unit each (Item 1-4) from  stock card when I sell a unit of PC (combine of Item 1-4)

---

Reply from Richard Rost:

You're looking to have items that are made up of other items? This is certainly POSSIBLE, but very tricky, and if you want to keep inventory levels on these items, it's going to require some serious VBA, too.

What I usually suggest in this case is to have a ProductT table that lists your products, and then a ComponentT table that lists your components. Each product can be made up of one or more components. This will be a MANY TO MANY relationship so you'll need a JUNCTION TABLE.

Then, in your inventory control, when you remove an item, you just subtract one unit from each of the components, using either SQL UPDATE statements, or RECORDSETS. So yes, it's certainly possible, but this is pretty complex stuff.

You could also, alternatively, use a SELF JOIN and have components just be other products, but that makes it even trickier.