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?
