I have a header table with vendor number, invoice number, invoice date as the key fields and invoice amount and tax amount as data fields. I have a detail table with vendor number, invoice number,invoice date, account number as the key and account amount as the data. I want to update the tax amount in the header with the results from the following query:SELECT tbl_Inv_Detail.Vendor_No, tbl_Inv_Detail.Invoice_No, tbl_Inv_Detail.Invoice_Date, SUM(CASE tbl_Taxes.Type WHEN 'G' THEN tbl_Inv_Detail.GL_Amount ELSE 0 END) AS GST, FROM tbl_Inv_Detail LEFT OUTER JOIN tbl_Taxes ON tbl_Inv_Detail.GL_Account = tbl_Taxes.Tax_ID GROUP BY tbl_Inv_Detail.Vendor_No, tbl_Inv_Detail.Invoice_No, tbl_Inv_Detail.Invoice_Date I have been trying to find a way to code this so my query would look something like this: UPDATE tbl_Inv_Header SET tbl_Inv_Header.Tax = GST FROM (select query ) but I am not completely sure of the syntax. could some one help? Thanks, Tom
UPDATE H SET H.Tax = d.GST from tbl_Inv_Header as H inner join (select query) as don H.Invoice_no=D.Invoice_no and ....