468,253 Members | 1,297 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,253 developers. It's quick & easy.

UPDATE query not working because of aggregate query

Seth Schrock
2,951 Expert 2GB
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
2 902
jforbes
1,107 Expert 1GB
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
2,951 Expert 2GB
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.

Similar topics

2 posts views Thread by D Newsham | last post: by
6 posts views Thread by Nicolae Fieraru | last post: by
reply views Thread by kermitthefrogpy | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.