Help

L

Lisa

I am trying to combine or total my 3 price per ounce columns by each item.
thenI need a grand total of what the price is per item. Here is my query.
Please help.
Thank you
SELECT tblIngredients.MenuItemNumber, tblIngredients.NumberofOunces,
tblIngredients.Ingredient, tblIngredients.USFoodNumber,
qryCurrentPricePerLbsToOz.PricePerOz,
qryTotalCostforInHouseItems.PerOuncePrice, tblMenuCatergory.Catergory,
tblMenuCatergory.CatergoryNumber, tblMenuItems.MenuItems, tblMenuItems.Price,
qryNonUSFoodItems.PricePerOunce, tblMenuItems.ID
FROM (tblMenuItems INNER JOIN tblMenuCatergory ON tblMenuItems.MenuCatergory
= tblMenuCatergory.Catergory) INNER JOIN (((tblIngredients LEFT JOIN
qryTotalCostforInHouseItems ON tblIngredients.Ingredient =
qryTotalCostforInHouseItems.ItemName) LEFT JOIN qryCurrentPricePerLbsToOz ON
tblIngredients.USFoodNumber = qryCurrentPricePerLbsToOz.[USF Product Number])
LEFT JOIN qryNonUSFoodItems ON tblIngredients.USFoodNumber =
qryNonUSFoodItems.ManufCode) ON tblMenuItems.ID =
tblIngredients.MenuItemNumber
ORDER BY tblMenuCatergory.CatergoryNumber;
 
J

Jeff Boyce

Lisa

I'm having a bit of trouble visualizing your underlying table structure ...
and it all starts with the data!

Could you post an idea of the data structure, and perhaps an example of the
data?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

John Spencer

SELECT tblIngredients.MenuItemNumber
, tblIngredients.NumberofOunces
, tblIngredients.Ingredient
, tblIngredients.USFoodNumber
, qryCurrentPricePerLbsToOz.PricePerOz
, qryTotalCostforInHouseItems.PerOuncePrice
, tblMenuCatergory.Catergory
, tblMenuCatergory.CatergoryNumber
, tblMenuItems.MenuItems, tblMenuItems.Price,
, qryNonUSFoodItems.PricePerOunce
, tblMenuItems.ID

, NZ(qryCurrentPricePerLbsToOz.PricePerOz,0)
+ NZ(qryTotalCostforInHouseItems.PerOuncePrice,0)
+ NZ(qryNonUSFoodItems.PricePerOunce,0) as ThreeAdded

FROM (tblMenuItems INNER JOIN tblMenuCatergory ON tblMenuItems.MenuCatergory
= tblMenuCatergory.Catergory) INNER JOIN (((tblIngredients LEFT JOIN
qryTotalCostforInHouseItems ON tblIngredients.Ingredient =
qryTotalCostforInHouseItems.ItemName) LEFT JOIN qryCurrentPricePerLbsToOz ON
tblIngredients.USFoodNumber = qryCurrentPricePerLbsToOz.[USF Product
Number])
LEFT JOIN qryNonUSFoodItems ON tblIngredients.USFoodNumber =
qryNonUSFoodItems.ManufCode) ON tblMenuItems.ID =
tblIngredients.MenuItemNumber
ORDER BY tblMenuCatergory.CatergoryNumber;

I'm not sure what you mean by "grand total of what the price is per item".

Heck, I'm not even sure that the above gives you the value you meant by
"total my 3 price per ounce columns by each item".
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
L

Lisa

That work perfectly.
Thank you
--
Lisa S.


John Spencer said:
SELECT tblIngredients.MenuItemNumber
, tblIngredients.NumberofOunces
, tblIngredients.Ingredient
, tblIngredients.USFoodNumber
, qryCurrentPricePerLbsToOz.PricePerOz
, qryTotalCostforInHouseItems.PerOuncePrice
, tblMenuCatergory.Catergory
, tblMenuCatergory.CatergoryNumber
, tblMenuItems.MenuItems, tblMenuItems.Price,
, qryNonUSFoodItems.PricePerOunce
, tblMenuItems.ID

, NZ(qryCurrentPricePerLbsToOz.PricePerOz,0)
+ NZ(qryTotalCostforInHouseItems.PerOuncePrice,0)
+ NZ(qryNonUSFoodItems.PricePerOunce,0) as ThreeAdded

FROM (tblMenuItems INNER JOIN tblMenuCatergory ON tblMenuItems.MenuCatergory
= tblMenuCatergory.Catergory) INNER JOIN (((tblIngredients LEFT JOIN
qryTotalCostforInHouseItems ON tblIngredients.Ingredient =
qryTotalCostforInHouseItems.ItemName) LEFT JOIN qryCurrentPricePerLbsToOz ON
tblIngredients.USFoodNumber = qryCurrentPricePerLbsToOz.[USF Product
Number])
LEFT JOIN qryNonUSFoodItems ON tblIngredients.USFoodNumber =
qryNonUSFoodItems.ManufCode) ON tblMenuItems.ID =
tblIngredients.MenuItemNumber
ORDER BY tblMenuCatergory.CatergoryNumber;

I'm not sure what you mean by "grand total of what the price is per item".

Heck, I'm not even sure that the above gives you the value you meant by
"total my 3 price per ounce columns by each item".
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Lisa said:
I am trying to combine or total my 3 price per ounce columns by each item.
thenI need a grand total of what the price is per item. Here is my query.
Please help.
Thank you
SELECT tblIngredients.MenuItemNumber, tblIngredients.NumberofOunces,
tblIngredients.Ingredient, tblIngredients.USFoodNumber,
qryCurrentPricePerLbsToOz.PricePerOz,
qryTotalCostforInHouseItems.PerOuncePrice, tblMenuCatergory.Catergory,
tblMenuCatergory.CatergoryNumber, tblMenuItems.MenuItems,
tblMenuItems.Price,
qryNonUSFoodItems.PricePerOunce, tblMenuItems.ID
FROM (tblMenuItems INNER JOIN tblMenuCatergory ON
tblMenuItems.MenuCatergory
= tblMenuCatergory.Catergory) INNER JOIN (((tblIngredients LEFT JOIN
qryTotalCostforInHouseItems ON tblIngredients.Ingredient =
qryTotalCostforInHouseItems.ItemName) LEFT JOIN qryCurrentPricePerLbsToOz
ON
tblIngredients.USFoodNumber = qryCurrentPricePerLbsToOz.[USF Product
Number])
LEFT JOIN qryNonUSFoodItems ON tblIngredients.USFoodNumber =
qryNonUSFoodItems.ManufCode) ON tblMenuItems.ID =
tblIngredients.MenuItemNumber
ORDER BY tblMenuCatergory.CatergoryNumber;
Click to expand...
Click to expand...
 
Top