I'm sorry I can't come up with a more descriptive title. I can't figure out the right words to use to learn to fix this myself.
Let's say I have three tables for an inventory management/profit calculation system.
First table has ID numbers for each item I use, and it matches those IDs up to human-readable names, for example:
Second table has the quantity of each item needed to create each final product, for examplePlease Login or Register to view this content.
Third table has prices that include multiple possible suppliers, for examplePlease Login or Register to view this content.
I have a query that will return the minimum price as follows:Please Login or Register to view this content.
But what I REALLY want to do is write a query that will take the item IDs, look up the minimum price of each, and calculate how much the final item will cost to make. In this example I want it to take the recipe I gave it in table #2 and understand that 4 tbs flour ($0.20) plus 4 tbs sugar ($0.40) plus 2 tbsp cocoa powder ($0.36) plus 2 tbs vegetable oil ($0.16) plus 2 tbs water (0) = $1.12 which would mean the brownie is $0.88 profit.Please Login or Register to view this content.
Whenever I try to do this, Access gets very antsy about trying to refer back to ID multiple times in the same record of the Recipe table. It will either return no matches or it will get on my case about ambiguous joins. And I have NO idea how to stop it, and NO idea what keywords I need to use to figure it out. I'm good at figuring things out with google and experimentation, but I can't even figure out how to start in this case.
Thanks in advance
By the way, the example here is is a super awesome Mug Brownie recipe if that's any encouragement to help, ha.
Bookmarks