Selling products and parts?

[LPB]

Hello,

I was hoping someone would be able to shed some light on how I can sell products and parts to the same customer?

I have a cookers table which includes ID, name and the price

I have a parts table which include all the relevant details about the parts.



And I have a cooker parts table which tells me how many parts are required to make one cooker.

This is all working fine, but the problem arises when I want to enter customer orders as a customer can buy x amount of cookers but they can also buy x amount of individual (spare) parts. Some orders will only be for cookers, and some orders will only be for individual parts.

I just can't work out how to lay the order form out, or how I would lay out a "View customer orders" form so I could view all of their orders and the relevant details. I have two tables set up to deal with an order, one containing the order details like the date, order number etc, and then another table to deal with the details so how many of what product is ordered. I was thinking that maybe I could split the order details table into a sell cookers and a sell parts table so that I could enter the relevant quantity of each that has been ordered but this doesn't seem like it would be the most effective way to do it.

I did consider having a general inventory list where I listed cookers and parts together and just had another field to specify if it was a cooker or a part but then I couldn't work out how I would deal with selling 1 cooker and deducting the relevant quantities of parts from the stock levels.

Any advice or links to example databases doing this would be very much appreciated.

Thanks

[orange]

Tell us about your tables --their names and the fields in each.

If you step back, you could say that a Cooker and the XYZ spare part are just "items". So if you record items, your issue may be resolved. Another option is to create a part hierarchy where every Part that is required to make a Cooker (and the quantity of each) is related to the cooker.

Have you created a "mock up" of an Order form with various items (parts and cooker)?

[ranman256]

Cooker is a part. put it in the tParts table.
That part has parts. A self relating table.