As i mentioned in comment to the question, one possible reason that your query does not work is that you forgot to add
GROUP BY
clause together with
AVG()
function. Finall query should look like:
SELECT t1.IngredientLotId1, t1.IngredientName, t1.ActualWeight, t1.TargetWeight, ROUND((t1.ActualWeight- t1.TargetWeight ),0) AS VARIANCE,
ROUND( (0.04* t1.TargetWeight ),0) AS TOLERANCE, ROUND(([t1].[TargetWeight]),0) AS TargetWeight, AVG(t1.TargetWeight) AS AvgTargetWeight
FROM CompletedIngredients As t1 INNER JOIN CompletedFormulas As t2 ON t1.RecordNumber=t2.RecordNumber
GROUP BY t1.IngredientLotId1, t1.IngredientName, t1.ActualWeight