I have tables with the following relationship:
1. tbl_RecipeList with recipeID as PK
2. tbl_MasterRecipe with recipeID as PK and ingredientID as PK
3. tbl_PriceList with ingredientID as PK
4. tbl_CostVersion with CostVersionID as PK and ingredientID as PK and costnumber as one of the field
My goal is to populate the ingredientID with the cost in the tbl_CostVersionID to include even if the costnumber is zero or null or doesn't exist. This way, I can track if the calculated recipe price in another query has no wrong value. Somehow, it seems like the Query Design View may need to be corrected with the proper outer join.
This is the Query so far:
SELECT tbl_MasterRecipe.ingredientID, tbl_RecipeList.recipeID, tbl_PriceList.NamaBBU, tbl_MasterRecipe.Qty_Kg, tbl_CostVersion.CostVersionID, tbl_CostVersion.costnumber
FROM tbl_RecipeList INNER JOIN ((tbl_PriceList LEFT JOIN tbl_CostVersion ON tbl_PriceList.ingredientID = tbl_CostVersion.ingredientID) LEFT JOIN tbl_MasterRecipe ON tbl_PriceList.ingredientID = tbl_MasterRecipe.ingredientID) ON tbl_RecipeList.recipeID = tbl_MasterRecipe.recipeID
WHERE (((tbl_RecipeList.recipeID)="recipenumber") AND ((tbl_CostVersion.CostVersionID)=2)) OR ((tbl_CostVersion.CostVersionID) Is Null)) ;
somehow it doesn't work as it only show the numbers that only exist.. Eventually, the numbers will be populated by the recipeID and CostVersionID.
Any idea on how to properly address the JOIN statement?
1. tbl_RecipeList with recipeID as PK
2. tbl_MasterRecipe with recipeID as PK and ingredientID as PK
3. tbl_PriceList with ingredientID as PK
4. tbl_CostVersion with CostVersionID as PK and ingredientID as PK and costnumber as one of the field
My goal is to populate the ingredientID with the cost in the tbl_CostVersionID to include even if the costnumber is zero or null or doesn't exist. This way, I can track if the calculated recipe price in another query has no wrong value. Somehow, it seems like the Query Design View may need to be corrected with the proper outer join.
This is the Query so far:
SELECT tbl_MasterRecipe.ingredientID, tbl_RecipeList.recipeID, tbl_PriceList.NamaBBU, tbl_MasterRecipe.Qty_Kg, tbl_CostVersion.CostVersionID, tbl_CostVersion.costnumber
FROM tbl_RecipeList INNER JOIN ((tbl_PriceList LEFT JOIN tbl_CostVersion ON tbl_PriceList.ingredientID = tbl_CostVersion.ingredientID) LEFT JOIN tbl_MasterRecipe ON tbl_PriceList.ingredientID = tbl_MasterRecipe.ingredientID) ON tbl_RecipeList.recipeID = tbl_MasterRecipe.recipeID
WHERE (((tbl_RecipeList.recipeID)="recipenumber") AND ((tbl_CostVersion.CostVersionID)=2)) OR ((tbl_CostVersion.CostVersionID) Is Null)) ;
somehow it doesn't work as it only show the numbers that only exist.. Eventually, the numbers will be populated by the recipeID and CostVersionID.
Any idea on how to properly address the JOIN statement?