MS Access UPDATE Query errors

Question

I am having a hard time using MS Access because the syntax is a little finicky compared to other db's.

I am trying to validate a table and compare that table to a master table with multiple columns of information. At the moment I am trying to update a table with a field name of Difference_Value in table ct2011 to be equal to (ct2011.Distribution_Amount - AggregateFinal.SumOfDollars).

Also specifying the lines in which are going to be updated because not all rows in the MASTER are in the table ct2011.

Below is my query.

UPDATE ct2011
SET ct2011.Difference_Value = (ct2011.Distribution_Amount - AggregateFinal.SumOfDollars)
FROM
       ct2011 as ct
INNER JOIN 
       AggregateFinal af
ON
       ct.Employee_ID = af.EmpId AND ct.Legal_Name = af.LegalName AND ct.Distribution_Plan_Year = af.CalculationAwardPeriod AND ct.Award_Year = af.AwardPeriod;

I am getting a Syntax error (missing operator). It specifies that it is encountering the error during the SET expressions after the =.

And what exactly is not working with the given query? Is there any error message? — Nico Haase
– Nico Haase, Commented Feb 5, 2019 at 15:24
@NicoHaase I am getting a Syntax error (missing operator). It specifies that it is encountering the error during the SET expressions after the =. — A.Bell
– A.Bell, Commented Feb 5, 2019 at 15:31
You set a table alias of ct for ct2011, but kept using ct2011 to fully qualify the field names. — HardCode
– HardCode, Commented Feb 5, 2019 at 19:54
There is no FROM clause in UPDATE queries in MS Access. And before you claim that is finicky, MySQL also adheres to this same UPDATE ... JOIN style. Still finicky? Try writing a join in UPDATE query in Oracle which neither supports JOIN or FROM unless inside a subquery, similar also to SQLite. Yes, all SQL dialects are finicky! — Parfait
– Parfait, Commented Feb 5, 2019 at 20:23

Lee Mac · Accepted Answer · 2019-02-05 18:07:24Z

1

In an MS Access update query, the join criteria should follow the update keyword, e.g.:

update 
    ct2011 ct inner join aggregatefinal af on
    ct.employee_id = af.empid and 
    ct.legal_name = af.legalname and 
    ct.distribution_plan_year = af.calculationawardperiod and
    ct.award_year = af.awardperiod
set 
    ct.difference_value = (ct.distribution_amount - af.sumofdollars)

answered Feb 5, 2019 at 18:07

Lee Mac

16.1k6 gold badges38 silver badges84 bronze badges

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

Collectives™ on Stack Overflow

MS Access UPDATE Query errors

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related