I am running a small snack fund at work and i want to keep track of the money spent and received and also the products sold and bought. I buy most of my stuff from bulk stores like Sam's Club and Costco but also hit up the sales at the local grocery stores. I have the basic design layout completed but i am at a loss of how to track products that i buy that have more then one item in them. I buy boxes of candy bars in bulk that will have 6 different candy bars or a box of pastries that has various different kinds. When i add them to my list of purchases i need to be able to separate them into there single item form for resale. I want to track each item separately on the resale side for pricing and keeping track of what sells and what doesn't. So my question is do i have to have to have two different products tables one for purchased and one for resale and how would the relate to each other or is there a way to separate the items out automatically. For example I buy a variety box of candy bars that has 10 snickers, 6 milky ways, 6 three musketeers and 8 twixs. In the purchase details it would be listed as candy bar variety pack but for the resale side that does me no good. Here is my current relationships table with just one products table. Any help would be appreciated. My goal is to be able to enter each item as it appears on the sales receipt from the store and have the qty of products for resale update appropriately for each individual item.
Not sure if i need more tables some type of coding or what?