Cannot Add Record Join Key of Table Not in Recordset

[MarkDownMark]

Hmm..you're right. Try it MarkDownMark. Just remove the Recipe table from the CostPerIngredient query and see what happens.

I tried it real quick and it's giving me all kinds of other problems now. I will dig a little deeper in a few minutes and see what I can come up with. As of right now, it's not doing what I want it to but I might be able to clean it up...we shall see.

 

[RuralGuy]

If it was easy, anyone could do it!

 

[jdraw]

A couple of thoughts on general design.
-As RG has suggested I'd get rid of the lookup fields in the table. Use lookup Table(s) on form
-I'd suggest a common pricing unit---record PricePerOunce for ingredients
-current structure doesn't allow for Price changes--surely supplies/ingredients have price changes with time.
-I'd suggest including the number of ounces of ingredient at the line level and the PricePerOunce at the line level. That way you can always reprint an invoice without changing the ingredient pricing, Also the currentPrice or CurrentCost of the ingredient is the real definition of the Cost in the ingredient table. You might want to include a LastPurchsedDate on the ingredient table if that is important to you.
You may want to create an IngredientHistory table if quantities and costs are important for accounting/auditability.
I don't see any tables related to Customers, Orders, Invoices but perhaps that is not in scope.

I haven't looked at forms etc, because I feel the above issues are significant and may be contributing factors to some of the uncertainties/difficulties.

 

[MarkDownMark]

A couple of thoughts on general design.
-As RG has suggested I'd get rid of the lookup fields in the table. Use lookup Table(s) on form

I'm looking into doing this but I need to be able to have each recipe hold each ingredient in a table where it can be referenced to at a later time...which I haven't been able to do yet.

-I'd suggest a common pricing unit---record PricePerOunce for ingredients

I do this on the Add Ingredient Form. She puts in the pounds and/or ounces and cost for items and it auto calculates the ounces and then the cost per ounce.

-current structure doesn't allow for Price changes--surely supplies/ingredients have price changes with time.

Very good point that I hadn't really thought of but I'm not sure this is too necessary for her purposes. This whole thing was meant to give her a quick and easy solution to figuring out how much a new recipe might cost her to make.

-I'd suggest including the number of ounces of ingredient at the line level and the PricePerOunce at the line level. That way you can always reprint an invoice without changing the ingredient pricing,

Again, invoicing is not so important but I feel like I have done what you suggested...if it works. When you put in the ingredient for each recipe you put how much of that item you used. It then calculates how much that item costs per recipe.

-Also the currentPrice or CurrentCost of the ingredient is the real definition of the Cost in the ingredient table. You might want to include a LastPurchsedDate on the ingredient table if that is important to you.

I did intend to put a purchase date and location of purchase on the ingredient form. We thought about that yesterday as we were inputting ingredients in.

-You may want to create an IngredientHistory table if quantities and costs are important for accounting/auditability.

Not sure if this is important at this time.

-I don't see any tables related to Customers, Orders, Invoices but perhaps that is not in scope.

Again, not important at this time.

-I haven't looked at forms etc, because I feel the above issues are significant and may be contributing factors to some of the uncertainties/difficulties.

Thanks for at least looking at it and giving me some suggestions. I will implement what I can and keep working to fix the other issues.

 

[RuralGuy]

In your CostPerIngredient query you included the RecipeID field from the Recipe table and it should be the the RecipeID field from the RecipeLines table.

 

[MarkDownMark]

In your CostPerIngredient query you included the RecipeID field from the Recipe table and it should be the the RecipeID field from the RecipeLines table.

RG...I don't even know what to say. That little tweak is now allowing me to add ingredients to each recipe and it is storing it as I intended it to. I for the life of me would probably never have thought to go look there. My wife is super happy and amazed that there are people out there willing to help complete strangers. Your help was greatly appreciated!! Unfortunately you fixed it on my last day off so now I know what I'll be doing for the rest of the day. THANK YOU again!! And thank you to everyone else for your suggestions and help.

 

[RuralGuy]

You are certainly welcome. I enjoyed the challenge.

 

[jdraw]

-I'd suggest a common pricing unit---record PricePerOunce for ingredients
I do this on the Add Ingredient Form. She puts in the pounds and/or ounces and cost for items and it auto calculates the ounces and then the cost per ounce.


But you are storing Pounds and Ounces in the Ingredient table???

Also, for basic database/application design:
Get a 30,000 ft overview description of the business. Build a conceptual model -even if only black boxes for the major things involved. Keep it high level, but account for all the major pieces. Map out where the pieces fit and relate to one another.
Even if "Financials" (Invoices, Payments....) are not the immediate goal/priority, get them into the big picture, --you'll know where they fit (if when) you get to that part in future. You may end up with multiple Suppliers, just put the black box and linkages in the high level model.

For your own benefit, rethink the LineItem record and table. My experience indicates that storing the Unit cost/price and the quantity of units in the record is a good practice -which shows the quantity of units used/sold and the cost per unit in the transaction -- that is, the facts related to that line item are a permanent record. If you rely on the Cost/Price in the Ingredient table ( or Product table generally) you will lose the ability to reprint an Invoice and other things.

Audit and accounting may not be the current focus, but put the required black boxes into your conceptual model now, and it will save the re-analysis, redevelopment effort in the future. I've seen many examples of "jeez I wish I had thought of that before...." and "..damn they keep changing the specs.."

More info on Conceptual Data model

Good luck with your project.