Update Query with Sum Function (1 Viewer)

bmcgree1

Registered User.
Local time
Yesterday, 20:38
Joined
Jun 19, 2009
Messages
43
Hello Everyone. I'm trying to create an update query and have been failing. Here's the scenario - I have 2 tables: Order & OrderBreakdown. In my Order table I have a field named TotalCharge, the field I want to update. The OrderBreakdown table has InventoryID foreign key in it as well as a OrderID foreign key. The OrderBreakdown table is going to have multiple records, all with matching OrderID keys. In the OrderBreakdown table is a Subtotal field with the totals amounts for each record, I just need these all summed together (based on their OrderID's). But when I try to do this, access says "You tried to execute a query that does not include TotalCharge as an aggregate function". What am I missing here? I know what an aggregate function is, but Access won't let me choose the Totals button when I'm on an update query. The SQL i'm trying to use is below. Please help!!

UPDATE tbl_Order INNER JOIN tbl_OrderBreakdown ON tbl_Order.OrderID = tbl_OrderBreakdown.OrderID SET tbl_Order.TotalCharge = Sum([tbl_OrderBreakdown].[Subtotal])
WHERE (((tbl_Order.OrderID)=[forms]![frm_OrderBreakdown].[orderid]));
 

Users who are viewing this thread

Top Bottom