Use Aggregate in Update Query Access

I am trying to update an invoice total on an invoice table from the lineitem totals on an invoice details table. I'm finding using an aggregate function within an update query is challenging.

I'm adding these total fields to make it easier for management to gather stats in our small firm.

Here is the SQL that I think should work but it not working and throwing a: "Syntax Error (missing operator) in query expression 't.total from tblPO p inner join..."

UPDATE P
SET p.PO_total = t.total FROM tblPO p 
INNER JOIN
     (SELECT tblPO_Details.PO_ID, SUM(tblPO_Details.subtotal) as total
      FROM tblPO_Details
      GROUP BY tblPO_Details.PO_ID) as t
    ON t.PO_ID = p.PO_ID

Open in new window

Any ideas?

Microsoft AccessMicrosoft SQL Server

Last Comment

jerryb30

8/22/2022 - Mon

ClaudeWalker

12/27/2012

ASKER

I also tried this and it throws "must use updatable query"

UPDATE tblPO 
INNER JOIN
     (SELECT tblPO_Details.PO_ID, SUM(tblPO_Details.subtotal) as total
      FROM tblPO_Details
      GROUP BY tblPO_Details.PO_ID) as t
    ON t.PO_ID = tblPO.PO_ID
SET tblPO.PO_total = t.total

Open in new window

ASKER CERTIFIED SOLUTION

mbizup

12/27/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.