473,226 Members | 1,565 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

UPDATE query not working because of aggregate query

Seth Schrock
2,965 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 1226
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,965 Expert 2GB
I have tried the left join and it doesn't work.
Feb 15 '17 #3

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

Similar topics

2
by: D Newsham | last post by:
I am trying to run an update query and am having problems with null values. Starting from the beginning - I created a new table from my original table, selecting distinct combinations of these...
3
by: Bill Clark | last post by:
I have about 20,000 records pulled from Excel that I need to update. What I need to do is run an update query that bascially says: If a field is null, update it with the previous record value of...
5
by: Don Seckler | last post by:
I have an update query that runs when a report closes. I have several reports that will need to run the update query with diferent criteria. I'd like to simply make the criteria change in the...
6
by: Nicolae Fieraru | last post by:
Hi All, I was trying to update a field in a table, based on the results from a query. The table to be updated is tblCustomers and the query is qrySelect. This query has two parameters, provided...
0
by: ghadley_00 | last post by:
Run Update Query in silently in background I have a MS access 200 DB in which I have an update query which repalces all the " " with ''" in a field. I would like the update query to run in the...
1
by: DrSwens | last post by:
Using MS Access 2002/XP I am trying to create an update query which will count the number of times a particular item has been referenced in another table and store that value in the first table. For...
10
by: kwc | last post by:
I am pretty new at this, so please bear with me. I have a table with all my records. I update my table. Today, I updated my table with three new records. I want to take only those 3 records and...
0
by: nagar | last post by:
I'm using SQLite with the ADO.NET 2.0 SQLite Data Provider as the storage of my application. I chose to use an untyped dataset and I'm using a dataadapter to report changes to the DB. The query...
1
by: thomas msofe | last post by:
hello there; im trying to use the following piece of code but it doest work, UPDATE itermdesc SET qnty = ( SELECT qnty -1 FROM itermdesc WHERE itermcode =1001 ) WHERE itermcode =1001 it...
2
by: Arvinder Sahni | last post by:
UPDATE jt SET jt.Assigneduser = (SELECT u.userid FROM Users u where u.username ='Debbie Ross') FROM Jobstasks jt Users u on jt.assigneduser = u.userid Funds f on jt.fundid...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.