I have two Tables ("Shortages", "Orders"). Shortages contains each Material once, Orders is all the Material orders. I am trying to update Shortages field CurrentWeek with the sum of all orders of a material from Orders.
I am not an SQL guru and this is stumping me. I have googled things but I can't seem to get anything to work.
I can write a select query that gets me what I want with all the WHERE clauses but I can't get the values into the other table.
Any help is greatly appreciated!
It sounds like you want something like this:
UPDATE Shortages
SET CurrentWeek = (SELECT SUM(quantity)
FROM Orders
WHERE Orders.Material = Shortages.Material
AND <which records in Orders count toward the current Shortages record>)
WHERE <which records in Shortages you want to update>
Obviously you'll need to modify it with the proper column names.
Update Shortages Set CurrentWeek=(SELECT Orders.Material, Sum(Orders.Quantity) AS SumOfQuantity FROM Orders WHERE (((Orders.Plant)="N.Amer") And ((Left(Orders.Order_Number,4))<>"NA01") And ((Year([Ship_Date])*53+DatePart("ww",[Ship_Date]))=Year(Date())*53+DatePart("ww",Date()))) GROUP BY Orders.Material) But now I am being told I have "written a subquery that can return more than one field without using the EXISTS reserved word in the main query's from clause." It may be worth noting that at this moment I am utilizing Microsoft Access (if that helps).Orders.Material, Sum(Orders.Quantity) AS SumOfQuantity, which is two columns. The UPDATE statement works differently than you think: while it does update ALL records, the statement itself defines how to modify EACH record. Basically: for each record in Shortages set the CurrentWeek column to this value. The subquery must return a single value, which is why above I limited it with WHERE to filter it down to "one" material, i.e. "the material for the current row being updated", Shortages.MaterialUpdate Shortages Set CurrentWeek=(SELECT Sum(Orders.Quantity) FROM Orders WHERE (((Orders.Plant)="N.Amer") And ((Left(Orders.Order_Number,4))<>"NA01") And ((Year([Ship_Date])*53+DatePart("ww",[Ship_Date]))=Year(Date())*53+DatePart("ww",Date()))) and Orders.Material = Shortages.Material) Also, some of those criteria might need to be in the Where clause of the UPDATE statement, not the subquery. I've updated my answer with more syntaxGet the SUM() first and then perform an update join. Something like below (** It's a demo query but hope you understood the point)
update Shortages s
join (
select orderid, sum(order_qty) as total_qty
from orders
group by orderid ) xxx on s.orderid = xxx.orderid
set s.CurrentWeek = xxx.total_qty;
SELECT Orders.Material, Sum(Orders.Quantity) AS SumOfQuantity FROM Orders WHERE (((Orders.Plant)="N.Amer") AND ((Left([Orders].[Order_Number],4))<>"NA01") AND ((Year([Ship_Date])*53+DatePart("ww",[Ship_Date]))=Year(Date())*53+DatePart("ww",Date()))) GROUP BY Orders.Material;That is the select query I have that gives me what I want and groups it...I am trying to pass that grouped sums to the Shortages table...Material is the link between both tables. I will try a few more and see what I can post.