MS Access update a table with condition joining to another table

Sam OZ
Sam OZ used Ask the Experts™
I need to update an MS Access 2003 database  table based on a condition
I tried I tried
update tblA    set Fld1 = B.Fld1   from tblA A inner   join tblB B     on A.Fld2 = B.Fld2    where B.Fld3 not in ( '000' , '001')

Also I tried options from following (It works for oracle. But not for Access)
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
The order in Access (ACE) SQL is different:

INNER JOIN tblB AS B ON A.Fld2 = B.Fld2 
SET A.Fld1 = [B].[Fld1]
WHERE (((B.Fld3) Not In ('000','001')));

Open in new window

But you can create such a query simply be using the Create Query Design wizard.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial