Unanswered: Query design, Production Cost vs. Purchase Price
Was hoping someone could give me a little advice on how to go about a project I'm tackling in hopes of learning a bit more about Access. I've had some exposure in the past but nowhere near enough. To make this somewhat dreaded task more bearable I decided to make a little database for a game I play.
What I'm playing with is basically a Production Cost vs. Purchased Price database. I've a table in which I enter my "recipes" and another that holds the sales prices for various resources and their quantity. From there I made a query that calculates the unit cost of each resource. So far so good...
My next step was to make a query that ran through all the recipes and calculated how much the finished product would cost to make based on the cost of purchased materials. This worked out as well but then I hit a snag.
For the majority of the items the production cost is less than the sales price of the finished good. However since the production cost is based on purchasing, rather than manufacturing, the materials my costs are rather inaccurate. What results is something like this:
Wool Cloth: $6
Wool Thread: $1
The correct amount for the cloth would be $3, cloth being made from three threads in my game, but it bases the production cost off the purchase price for the thread of $2.
I've thought up a variety of amateur hour ways to work around this but felt there had to be a simple answer that's eluding me. It would appear I just need to add a portion that reiterates over the production cost comparing it to the sales price and recalculates the cost for items I can produce by myself for a lower price but...what's a good clean way to do this?
What's your table structure look like? I would create two tables for the actual "recipe" storage. One table for general information on the recipe, then another for the components of the recipe. In the components table, include a field for quantity. You can then derive ACTUAL cost based on quantity required for each given component required.
The Yield/Quantity columns are necessary for keeping track of costs on items that are produced in sets of three but only sold by the dozen.
The structure, due to the possible number of ingredients and their quantities, makes it somewhat unwieldy to look at but is working out well.
What were you envisioning being in the second "general" table? This little project of mine is based off a game so I'm not actually cooking anything, no oven settings, preperation instructions, etc. that would require a memo field or such.
I probably wasn't clear in my intial post so I'll try an example from the game that might help explain what I'm attempting.
You've a table that has the Sales Price for the following items:
We've another table that has the "Recipe" structure noted above. Our recipe for Wool Thread uses two Sheep Wool and the Wool Cloth uses three Wool Threads.
From here I made a query to calculate the unit cost of items in the Sales Price table. No biggie. From there I made a new query that uses those unit cost, and the data in the Recipes table, to calculate the Production Cost for each recipe. Not too many problems there except for having to break up big SQL select statements across multiple columns in the query due to the 1024 limit. Long story short that query spits out the following:
And that's my problem! The production cost of the Wool Thread is correctly calculated however, since the query and I both don't know how to use the information we just calculated, the production cost of the Wool Cloth is shown as $9.00 instead of $6.00. I.e., it's based on paying the $3.00 sales price for the thread instead of the $2.00 if I made it myself.
In the query's defense it's just doing exactly what I told it to but I need some advice on how, either through another query or what have you, to make it recalculate the Production Cost based on the lowest priced resources available.
Needless to say this is far from a mission critical application. Just a little something I'm working on in my spare time trying to get a better grasp of database mechanics.
It looks like you are trying to create a Bill Of Materials by rolling up the costs of components. Access isn't ideal for this without a fair bit of work.
If your problem is strictly based on two iterations then it shouldn't be too difficult. You would have two recipe tables. One for intermediate components and one for finished product (based on intermediate components). You would also have a materials table for your initial materials which also contain the (make/buy) cost. Be careful to differentiate between the make/buy cost and the selling price. Presumably you are only selling the finished product so only this table needs a selling price.
Somehow I suspect your problem is much more complex than I describe.
One other comment, you have identified your ingredient is separate columns. This makes life hard for you as it's difficult to add up the columns without long expressions. Consider this recipe structure: