running an update with outter join error

Question

This works fine in SQL, but when trying in access I get error.

Syntax error (missing operator) in query expression......

Below is my script

update j

set community = j.[location]

from [dbo].[cfeb7] j

left outer join directory_community d1 on j.[Community] = d1.community

left outer join directory_community d2 on j.[Location] = d2.community

where d1.community is null and d2.community is not null

Answer 1

In Ace-SQL we need parentheses when more than one JOIN is used. And the table to update must be named first. Also the name of the cfeb7 table is invalid in Access.

So the question is what are you trying exactly?

In Access it is:

UPDATE directory_community AS d1 
	RIGHT JOIN (
		cfeb7 AS j 
			INNER JOIN directory_community AS d2 
				ON j.location = d2.community
	) ON d1.community = j.community 
SET j.community = [j].[locaton]
WHERE (((d1.community) Is Null));

Answer 2

Why is the table name cfeb7 considered invalid?

I am aware that parentheses are needed, but not sure where and how based on my query.

I am wanting to do a match against the cfeb7 table and look at the location column.

For every record do a compare and confirm that value in that record exists in directory_community table (community) column, if it does, I want to update the cfeb7 location table with the community value.

Answer 3

Cause it an invalid object name in Access:

Answer 4

OK, truth be told the name i have was not exactly that so i didn't know that.

Still dont know what the issue is with that name.

The actually table name is Jobs.

Answer 5

Well, what's wrong in using the designer?

Answer 6

I am not familiar with using the desinger for doing outter joins, well any joins for that matter.

I come from the SQL query writing world.

Answer 7

Any thoughts on suggestions on how I can get this going?

Are you aware of parentheses needed?