UPDATE query not working because of aggregate query

[Seth Schrock]

I've got a really weird situation where I would like to use an UPDATE query to update multiple records simultaneously to the values in an aggregate query. What I am trying to do is join a temp table to an aggregate query and update the temp table with the sum of a field like this:

UPDATE qryInvoiceIngredients INNER JOIN tempFeed 
   ON (qryInvoiceIngredients.IngredientId_fk = tempFeed.IngredientId_fk) 
   AND (qryInvoiceIngredients.FeedId_fk = tempFeed.FeedId_fk) 
   AND (qryInvoiceIngredients.InvoiceId_fk = tempFeed.InvoiceId_fk) 
SET tempFeed.Pounds = qryInvoiceIngredients.SumOfPounds 
WHERE tempFeed.InvoiceId_fk = 1 
   And tempFeed.InvoiceItemId_fk = 1
 

qryInvoiceIngredients is an aggregate query. My problem is it is an aggregate query, it makes the whole thing not updateable and so it fails. I've gotten it to work using the DSum() function instead of joining qryInvoiceIngredients to the temp table, but I really don't like using domain aggregate functions in queries. Does anyone know any tricks to get around this?

[jforbes]

You should be able to use an aggregate query in an Update Query. Maybe the order of joining or the INNER join, probably the INNER join, is messing things up. Mind seeing if this will work?

UPDATE tempFeed LEFT JOIN qryInvoiceIngredients 

[Seth Schrock]

I have tried the left join and it doesn't work.