Solved

Use Aggregate in Update Query Access

Posted on 2012-12-27

1 solution

511 Views

Last Modified: 2012-12-27

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?

Comment

Question by:ClaudeWalker

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

4 Comments

Author Comment

by:ClaudeWalker

ID: 387248242012-12-27

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

LVL 61

Accepted Solution

by:mbizup

mbizup earned 500 total points

ID: 387248592012-12-27

As far as I know, you can't use a subquery in your UPDATE statement like this in Access.

A workaround is to use your aggregate query to make a temporary table (turn it into a make table query) and use that table as the source for your UPDATE.

Author Closing Comment

by:ClaudeWalker

ID: 387249052012-12-27

That's the only thing that worked :)

LVL 26

Expert Comment

by:jerryb30

ID: 387249412012-12-27

UPDATE tblPO AS a SET a.po_total = DSum("subtotal","tblPO_Details","po_id = " & [a].[po_id]);

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Finding the Office Templates Folder

Article by: Helen

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …

Automating Microsoft Excel from Access (Part II)

Article by: Dale Fye

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.

SQL - The SELECT Statement

Video by: Zia

Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

Microsoft SQL Server

SQL - Updating and Deleting Data

Video by: Zia

Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…