Need to debug Left Join in SQL

Question

Help, I'm new to SQL, and new to Stack Overflow!

I am receiving a "Syntax Error", which only seems to occur with the 2nd Left Join (without it, the code runs fine). Here is my code, being compiled by Access 2010:

SELECT      Billings.[CustomerNumber], 
            Billings.[Customer Name], 
            Billings.[SOP Number], 
            Billings.[Item Number], 
            Sum (Billings.[Extended Price]) AS ['Revenue'], 
            Sum (Billings.QTY) AS ['Quantity']
FROM        [Product Codes]         AS Codes
LEFT JOIN   Billings                ON Codes.[Product Code] = Billings.[Item Number]
LEFT JOIN   [All Rep Assignments]   ON Billings.CustomerNumber = [All Rep Assignments].[Account Number]
GROUP BY    Billings.[CustomerNumber], 
            Billings.[Customer Name], 
            Billings.[SOP Number], 
            Billings.[Item Number]

if you are using Access, why not use the query-builder to create your query visually, then you can see how the builder constructs the clauses :) — Tim, Jul 16 '14 at 16:41

score 0 · Answer 1 · edited May 23 '17 at 12:29

If your SQL as shown is from the Access query builder using SQL view, be aware Access is sensitive about white-space. Make sure your lines end with a space. (I notice there is no semi-colon at the end, which is required in that view.)

If your code is from somewhere else, like a VBA module, then move that SQL over to the Access query builder using SQL view and trouble-shoot from there. (With a semi-colon at the end.) See here for more.

score 0 · Accepted Answer · edited May 23 '17 at 12:21

You need parentheses when having multiple joins (1 for each)

SQL multiple join statement

SELECT      Billings.[CustomerNumber], 
            Billings.[Customer Name], 
            Billings.[SOP Number], 
            Billings.[Item Number], 
            Sum (Billings.[Extended Price]) AS ['Revenue'], 
            Sum (Billings.QTY) AS ['Quantity']
FROM        (([Product Codes]         AS Codes
LEFT JOIN   Billings                
ON Codes.[Product Code] = Billings.[Item Number])
LEFT JOIN   [All Rep Assignments]   
ON Billings.CustomerNumber = [All Rep Assignments].[Account Number])
GROUP BY    Billings.[CustomerNumber], 
            Billings.[Customer Name], 
            Billings.[SOP Number], 
            Billings.[Item Number]

Need to debug Left Join in SQL

2 Answers2