By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

UPDATE query not working because of aggregate query

Seth Schrock
Expert 2.5K+
P: 2,937
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:
Expand|Select|Wrap|Line Numbers
  1. UPDATE qryInvoiceIngredients INNER JOIN tempFeed 
  2.    ON (qryInvoiceIngredients.IngredientId_fk = tempFeed.IngredientId_fk) 
  3.    AND (qryInvoiceIngredients.FeedId_fk = tempFeed.FeedId_fk) 
  4.    AND (qryInvoiceIngredients.InvoiceId_fk = tempFeed.InvoiceId_fk) 
  5. SET tempFeed.Pounds = qryInvoiceIngredients.SumOfPounds 
  6. WHERE tempFeed.InvoiceId_fk = 1 
  7.    And tempFeed.InvoiceItemId_fk = 1
  8.  
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?
Feb 15 '17 #1
Share this Question
Share on Google+
2 Replies


jforbes
Expert 100+
P: 1,107
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?
Expand|Select|Wrap|Line Numbers
  1. UPDATE tempFeed LEFT JOIN qryInvoiceIngredients 
Feb 15 '17 #2

Seth Schrock
Expert 2.5K+
P: 2,937
I have tried the left join and it doesn't work.
Feb 15 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.