Inner Join Query in Access2013 SQL view

Question

I have two tables in access 2013"2017FrenchProjectData" and "NewModes" I am trying to use the 'invoicemode / service level' column that is in both fields to update the 'French Project Mode' that is only in the New Modes table. I am using the below however i keep getting a syntax error (missing operator) message.

UPDATE     [2017FrenchProjectData]
SET        [2017FrenchProjectData].[FrenchProjectMode]=newmodes.[frenchprojectmode]
          ,[2017FrenchProjectData].[BasicMode]=[newmodes].[modedesc]
FROM       [2017FrenchProjectData]
INNER JOIN NewModes 
ON         2017FrenchProjectData.[ Invoice Mode / Service Level]=Newmodes.[ Invoice Mode / Service Level]

I believe the syntax for Access requires the INNER JOIN after the SET with no FROM. — Jacob H
– Jacob H, Commented Jan 16, 2018 at 18:47

Erik A · Accepted Answer · 2018-01-16 19:05:31Z

1

The UPDATE syntax for Access is a little curious. It doesn't have a From clause. Instead, tables and joins are specified after specifying UPDATE:

UPDATE     [2017FrenchProjectData]
INNER JOIN NewModes 
ON         [2017FrenchProjectData].[ Invoice Mode / Service Level]=Newmodes.[ Invoice Mode / Service Level]
SET        [2017FrenchProjectData].[FrenchProjectMode]=newmodes.[frenchprojectmode]
          ,[2017FrenchProjectData].[BasicMode]=[newmodes].[modedesc]

edited Jan 16, 2018 at 19:05

answered Jan 16, 2018 at 18:47

Erik A

32.8k13 gold badges49 silver badges75 bronze badges

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

2 Comments

Preston Stokes Over a year ago

Thanks, Erik. It seems to be not liking the ON command as it gave me the synax error after adjusting to the above. I am getting "Syntax Error (Missing operator) in query expression '2017FrenchProjectData.[Invoice Mode / Service Level]=Newmodes.[invoice Mode / Service Level]

Erik A Over a year ago

Well, you have some weird (and likely incorrect) column names. Everything inside the square brackets is space sensitive, so your column names start with a space, then the text invoice mode, a space, a forward slash, another space, and then the text service level

Collectives™ on Stack Overflow

Inner Join Query in Access2013 SQL view

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related