CODEWITHSUNDEEP
sqlms-access
user1161512
user1161512

Reputation: 41

Syntax error (missing operator)

I am not a trained programmer, so trying to do this is difficult. I am getting the above error with this SQL code, and I cannot figure out the reason. Please advise.

SELECT a.agentname as 'Salesforce AgentName', a.loannumber, ag.agentname as 'portal agentname'
from salesforce a 
inner join portal b ON a.loannumber = b.loannumber

left join agents ag ON b.agentid = ag.agentid
where a.agentname <> ag.agentname

Upvotes: 4

Views: 1076

Answers (2)

HansUp
HansUp

Reputation: 97131

Access' db engine has strict requirement for parentheses when your SELECT statement includes more than 1 join. I suggest you start with a simplified version of the query (discard the field list and the WHERE clause) so you can focus on getting the joins right.

SELECT *
FROM
    (salesforce AS a
    INNER JOIN portal AS b
    ON a.loannumber = b.loannumber)
    LEFT JOIN agents AS
    ag ON b.agentid = ag.agentid;

Assuming that version works, try this:

SELECT
    a.agentname AS [Salesforce AgentName],
    a.loannumber,
    ag.agentname AS [portal agentname]
FROM
    (salesforce AS a
    INNER JOIN portal AS b
    ON a.loannumber = b.loannumber)
    LEFT JOIN agents AS
    ag ON b.agentid = ag.agentid
WHERE a.agentname <> ag.agentname;

Notice I enclosed the aliases for the column names with square brackets instead of single quotes. The single quotes would not trigger an error, but they would be included in the column header ... which I doubt you want.

You would do yourself a favor by building Access queries in Access' query designer. It will make it difficult to create a query with syntax the db engine won't accept.

Edit: If the purpose of this query is to compare agentname values from the salesforce and agents tables, perhaps you may want to try this as the WHERE clause:

WHERE a.agentname <> ag.agentname OR ag.agentname Is Null;

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453990

Bit of a guess (based on here)

SELECT a.agentname  AS 'Salesforce AgentName',
       a.loannumber,
       ag.agentname AS 'portal agentname'
FROM   ((salesforce as a )
       INNER JOIN portal as b
         ON a.loannumber = b.loannumber)
       LEFT JOIN agents ag
         ON b.agentid = ag.agentid
WHERE  a.agentname <> ag.agentname

BTW it is usually an error to refer to outer joined table columns in the WHERE clause unless you account for the possibility that they are NULL

Upvotes: 2

Related Questions