MS Access: Update statement with several joins

Question

I am trying to update a table (DMS_TEST_LOAD) based on two other tables (TDCE_NE and TDCE_NE_COMP). Any idea what is wrong with the below query? I keep getting the useless "Syntax error in UPDATE statement." error from Access. Thanks in advance.

UPDATE DMS_TEST_LOAD AS DMS 
((INNER JOIN TDCE_NE_COMP AS COMP
ON COMP.[NAME] LIKE DMS.[Trunk Group Number (TGN)] AND COMP.[NE_COMP_TYPE_ID]=421)
INNER JOIN TDCE_NE AS NE ON NE.[ID]=COMP.[NE_ID] AND NE.[NAME]=DMS.[Office])
SET DMS.[Char Parm 1 (Trk Dir)] = COMP.[CHAR_PARM1];

Why don't you just build it in Access "Design view" ? It will write the statement for you. — iDevlop, Oct 10 '14 at 17:11
@iDevlop because Access will develop the query in the simplest, most inefficient way possible and my query will take forever to run. — user1947544, Oct 10 '14 at 17:49
@user1947544 perhaps, but it can be a good starting point and a time saver to build a first version that works. — iDevlop, Oct 10 '14 at 22:32

score 1 · Answer 1 · answered Oct 10 '14 at 17:03

1

Not too sure what SQL system MS access uses. But you might want to try this

UPDATE DMS
SET DMS.[Char Parm 1 (Trk Dir)] = COMP.[CHAR_PARM1]
FROM DMS_TEST_LOAD AS DMS
 INNER JOIN TDCE_NE_COMP AS COMP
   ON COMP.[NAME] LIKE DMS.[Trunk Group Number (TGN)]
      AND COMP.[NE_COMP_TYPE_ID]=421
  INNER JOIN TDCE_NE AS NE
     ON NE.[ID]=COMP.[NE_ID] AND NE.[NAME]=DMS.[Office];

answered Oct 10 '14 at 17:03

Alex

371
1
8

No luck, @Alex. Unfortunately, MS Access doesn't have intuitive syntax such as that. I believe the joins must come prior to setting DMS.[Char Parm 1 (Trk Dir)]. – user1947544 Oct 10 '14 at 17:07
found an example here : http://www.fmsinc.com/microsoftaccess/query/snytax/update-query.html#UPDATE_Query_SQL_Syntax UPDATE tblAddress INNER JOIN tblZipCodes ON [tblAddress].[ZipCode] = [tblZipCodes].[ZipCode] SET [tblAddress].[City] = [tblZipCodes].[City], [tblAddress].[State] = [tblZipCodes].[State] WHERE ([Country] = 'US') – Alex Oct 10 '14 at 17:13
could the problem be with your like? would you need to compare a string with another string? http://stackoverflow.com/questions/14236410/access-query-where-one-field-is-like-another – Alex Oct 10 '14 at 17:20
I think the like statement is part of the issue. I am trying to formulate my join statement properly now, but I still keep getting the same error. As an update, I have using all of the proposed solutions (from Alex, Rich Andrews, and Linger) with no success; it's the same syntax error. – user1947544 Oct 10 '14 at 17:57
maybe cast the field to the left of the like to string like this CStr(DMS.[Trunk Group Number (TGN)]) ? – Alex Oct 10 '14 at 18:10

Linger · Answer 2 · 2014-10-10T17:29:23.950

In MS Access you have to make sure you use the parenthesis correctly in the joins. I do believe you are after the following:

UPDATE DMS
SET DMS.[Char Parm 1 (Trk Dir)] = COMP.CHAR_PARM1
FROM (DMS_TEST_LOAD AS DMS
  INNER JOIN TDCE_NE_COMP AS COMP 
    ON (COMP.NAME = DMS.[Trunk Group Number (TGN)]) AND (COMP.NE_COMP_TYPE_ID = 421)) 
  INNER JOIN TDCE_NE AS NE 
    ON (NE.Name = DMS.Office) AND (COMP.NE_ID = NE.ID)

Note I removed the LIKE and replaced it with =. You were not using any wild cards so it wasn't even functioning as a LIKE. If you want to use the LIKE you may be looking for the following:

COMP.NAME LIKE "*" & DMS.[Trunk Group Number (TGN)] & "*"

score 0 · Answer 3 · answered Oct 10 '14 at 17:07

How about something like this...

UPDATE DMS
SET [Char Parm 1 (Trk Dir)] = COMP.[CHAR_PARM1] 
FROM DMS_TEST_LOAD DMS
INNER JOIN TDCE_NE_COMP AS COMP
    ON COMP.[NAME] LIKE DMS.[Trunk Group Number (TGN)] 
    AND COMP.[NE_COMP_TYPE_ID]=421
INNER JOIN TDCE_NE AS NE 
    ON NE.[ID]=COMP.[NE_ID] 
    AND NE.[NAME]=DMS.[Office]

Not tested but refactored to ANSI92 "standard" SQL

And can be tested (without actually doing the update ofc) with...

SELECT [Char Parm 1 (Trk Dir)],  COMP.[CHAR_PARM1] 
FROM DMS_TEST_LOAD DMS
INNER JOIN TDCE_NE_COMP AS COMP
    ON COMP.[NAME] LIKE DMS.[Trunk Group Number (TGN)] 
    AND COMP.[NE_COMP_TYPE_ID]=421
INNER JOIN TDCE_NE AS NE 
    ON NE.[ID]=COMP.[NE_ID] 
    AND NE.[NAME]=DMS.[Office]

MS Access: Update statement with several joins

3 Answers3