CODEWITHSUNDEEP
sqlms-access
user2497624
user2497624

Reputation: 159

Error with the sum function in sql with Access

I am trying to sum the total price from invoices (named Total_TTC in table FACT) depending on the code of the taker ( named N_PRENEUR in the two concerned tables) and store the result in the DEBIT_P column of the table table_preneur. Doing so i get a syntax error (missing operator) In access and can't seem to understand why. I tried other posts and the usggestions returned me the same error.

    UPDATE P 
SET DEBIT_P = t.somePrice
FROM table_preneur AS P INNER JOIN
 (
  SELECT
   N_PRENEUR,
    SUM(Total_TTC) somePrice
  FROM
     FACT
  GROUP BY N_PRENEUR
  ) t
ON t.N_PRENEUR = p.N_PRENEUR

thx in advance

Upvotes: 1

Views: 1042

Answers (3)

JsonStatham
JsonStatham

Reputation: 10384

    with cte as
    (select t.somePrice
    from table_preneur as P
    inner join (select SUM(Total_TTC) as somePrice
      from FACT
      group by N_PRENEUR) t  
    on t.N_PRENEUR = p.N_PRENEUR)

    update P 
    set DEBIT_P = cte.somePrice
    -- DO YOU NEED A WHERE CLAUSE?

    --or maybe

    update table_preneur
set DEBIT_P = (select t.somePrice
from table_preneur as P
inner join (select SUM(Total_TTC) as somePrice
  from FACT
  group by N_PRENEUR) t  
on t.N_PRENEUR = p.N_PRENEUR)

Upvotes: 2

Julian Joseph
Julian Joseph

Reputation: 174

Just to clarify the above, in MS Access SQL, you need to use AS when declaring a table alias - this is missing from your SQL (SUM(Total_TTC) AS somePrice)

Upvotes: 1

Mansfield
Mansfield

Reputation: 15170

You're missing the as keyword before your column alias somePrice:

UPDATE P 
SET DEBIT_P = t.somePrice
FROM table_preneur AS P INNER JOIN
 (
  SELECT
   N_PRENEUR,
    SUM(Total_TTC) as somePrice
  FROM
     FACT
  GROUP BY N_PRENEUR
  ) t
ON t.N_PRENEUR = p.N_PRENEUR

Upvotes: 1

Related Questions