Access - query update with Inner Join

Question

I have this query:

UPDATE client
SET client.[client_history] = 10
FROM  [T_CLIENT] AS client
     INNER JOIN (SELECT [client_id], SUM([final_price])
            FROM [T_PURCHASE]
            GROUP BY [client_id]) AS p
     ON client.[client_id] = p.[client_id]

When i execute this query on access, i get "Syntax Error". Did you see something wrong?

Thank you

You're not using the subquery that gets joined at all. If you share a more realistic attempt, I could show you how to replace a joined subquery with a domain aggregate — Erik A
– Erik A, Commented Apr 29, 2018 at 21:06
Eh... Explain to me what that joined subquery is doing, then. You're not using any of it's fields, why is it there? — Erik A
– Erik A, Commented Apr 29, 2018 at 21:44
I wanna join all list of purchases of one client, after that, i wanna save the total amount in table client column, field client_history. — Juan.Queiroz
– Juan.Queiroz, Commented Apr 29, 2018 at 21:50

Erik A · Accepted Answer · 2018-04-29 21:58:33Z

2

You can use a DSUM to sum from a different table in an update query. Subqueries with aggregates won't work, because they're not updateable.

UPDATE t_client
SET [client_history] = DSUM("final_price", "T_PURCHASE", "client_id = " & client_id)

answered Apr 29, 2018 at 21:58

Erik A

32.8k13 gold badges49 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-04-29 20:37:06Z

2

Does the syntax work without FROM:

UPDATE [T_CLIENT] AS client INNER JOIN
       (SELECT [client_id], SUM([final_price])
        FROM [T_PURCHASE]
        GROUP BY [client_id]
       ) AS p
      ON client.[client_id] = p.[client_id]
    SET client.[client_history] = 10;

answered Apr 29, 2018 at 20:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

Olivier Jacot-Descombes Over a year ago

Yes, and probably he want something like SUM([final_price]) AS the_sum and then SET client.[client_history] = the_sum;, i.e. the alias is required to be able the refer to the sum. Relying on column names given automatically by Access is dangerous, because they can differ in different language versions of Access.

Erik A Over a year ago

That won't work, since the query won't be updatable. You can usually replace INNER JOIN and SUM with DSUM, but I'm a little unclear on the question since the joined subquery doesn't get used

Juan.Queiroz Over a year ago

Yes, i get an error: "Operation must use an updateable query" Anyone knows?

Harun24hr Over a year ago

@Gordon Linoff This question has similar issue. It says Operation must use an updateable query. Can you please have a look.

Collectives™ on Stack Overflow

Access - query update with Inner Join

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related