Best way to tackle inventory control? I have several ideas...need help.
Ok i need to tackle setting up some inventory control - but - im unsure what idea to move forward with. i have items that have both size and color options.
i have 2 tables in the database
1 table is the main product (SHIRT)
1 table is the subitems with the size/color/inventory (Size: LARGE, Color: BLUE, Inventory: 5)
on the product display page i plan to display only items that have > 0 inventory. My thought was to use a JOIN statement in the recordset to pull info from what has been ORDERED already. IE INVENTORY minus ORDERED = available amount.
My issue here is - HOW do i do the subtraction?
I have used before SUM, MAX, COUNT etc - but how do you subtract? It doesnt seem there is any function called DIFFERENCE.
another idea was to update the inventory field after the order is placed. (IE have this occur on the success page). However - this seems to be tricky when there is more than 1 item that is ordered. Im stumped as to how i would pull the recordset of multiple items - or do one at a time and have a recordset for each.
I need some guidance on which is the best idea, if there are better ideas, and HOW TO MOVE FORWARD ON WHICHEVER IS THE BEST.
Please assume I am dumb as a rock and give me the extreme basic 123 steps of how I should approach this.