Pricing Database

[nsvorp]

Hi,

I work at a small food service business where we make a number of different deli salads and similar things. One of my jobs is to price out how much each different salad costs to make. What I currently use is an Excel spreadsheet where I manually enter the cost of each ingredients and amount used to figure out cost. However, I would like to make an Access database to do this instead, where I have a form where I pull up a recipe, select all the ingredients and then have the form automatically calculate the cost.

I have made all the tables I will need, but I'm having trouble making the form where the costs are calculated. The calculating form needs to pull information from several different tables (materials, labor, and ingredients) to calculate the total cost of a recipe, and I'm having trouble figuring out a.) how to get all of that info on one form and b.) getting the form to calculate the correct values.



I have looked for templates that do something similar but I have had no luck. I would appreciate any suggestions, advice, or gentle shoves to resources on where to figure out how to do something like this!

Thanks!
Nick

[RuralGuy]

I've been trying to do this for years without success. Excel is actually better at this in my experience so far. It is a Bill of Materials problem (BOM). Google can find references for you.

[ajetrumpet]

Nick,

I'm not gloating by any means, but one of my best qualities is being able to manipulate code to do just about anything within the language's boundaries.

If you want to post your tables, or a relationship page screenshot, I might be able to give you a tip or two on how to get it done (from where you're currently at with it). It might be a solution, but probably won't be the best in terms of "accepted" techniques.

I think that MS's templates are maybe becoming obsolete too. Not to excess, but the structural makeup of and access database nowadays can be accepted or not, depending on who is using it.

[nsvorp]

RG - thanks for the answer and direction, I've looked some stuff up and have ideas if I can't get this working.

Adam - Feel free to gloat if you want if you can help, haha. I'm not a big believer in "correct" as long as it works, so any tips would be appreciated. I am posting a screenshot of the relationships page.

[AndreQ1]

What threw me is the denormalized structure (Ingredient 1, 2; material 1, 2, etc). If you show listboxes for each, and then add them to a table called RecipeCost with columns RecipeCostID (Autonumber) RecipeID (foreign key), CostType (Ingredient, Labor, Material), CostID (foreign key to that table), ItemPrice. You should have most of it there.

Then you can create listboxes of Ingredients, Labor, Material with an On Click that will copy it over to he RecipeCost table. Then the query is a simple sum for that RecipeID.

Hope I'm not going in too much of a new direction.
Andre

[ajetrumpet]

i Think I'd have to see your actual file or the form. Are those all one to one relate's?

Have you considered breaking out that recipe table? IMO, it's a little too big. E.G. - An alternate setup:

TABLE LABOR (to record labor needs)
*ID
*recipe ID (fk of material table)
*laborer
*hours
*rate
*cost (if not calculated...if so, use a query - dont' list here)

TABLE MATERIALS
*ID
*recipe ID (fk of material table)
*amount
*price
*cost

TABLE INGREDIENT
*ID
*recipe ID (fk of material table)
*quant
*unit
*rate
*cost

TABLE RECIPE
*EVERYTHING ELSE that's now in the recipe table


see the pattern? This way you can provide the db with one-to-many relate's only. things might be easier to query out and display. (just a thought!)

[nsvorp]

Thanks Andre and Adam for the help. I think I have a sense of the direction I need to go in now, which is really what I needed.

Cheers!
Nick