CODEWITHSUNDEEP
sqlms-accesssyntax
Firestar001
Firestar001

Reputation: 57

Access syntax error missing operator

So I'm working in access trying to make a table. I've got all the code set up, but when I try to view the database, it gives me an error.

Syntax error (missing operatior) in query expression 's.SupplierID = p.SupplierID LEFT JOIN OrderDetails AS od     
on p.ProductID = od.ProductID LEFT JOIN Orders AS o     
on od.OrderID = o.OrderID LEFT JOIN Customers AS c     
on o.CustomerID = c.CustomerI'

However here is the full code:

Select  p.ProductName AS 'Out of Stock Product', o.OrderID, c.CompanyName, c.ContactName, c.Address + ' ' + c.City +', ' + IsNull(c.StateOrRegion+ ', ', '')+ c.Country+', ' + c.PostalCode AS 'ADDRESS'
From Suppliers AS s LEFT JOIN Products AS p
on s.SupplierID = p.SupplierID LEFT JOIN OrderDetails AS od 
on p.ProductID = od.ProductID LEFT JOIN Orders AS o 
on od.OrderID = o.OrderID LEFT JOIN Customers AS c 
on o.CustomerID = c.CustomerID
    Where Discontinued = 1
    Order By p.ProductName

(as for some reason it cuts off the 'D' in the last CustomerID thing)

Any ideas on where I'm going wrong?

EDIT: So I found out that I'm supposed to add parenthesis to the joins, but now it's giving me another problem.
Updated code:

Select  p.ProductName AS 'Out of Stock Product', o.OrderID, c.CompanyName, c.ContactName, c.Address + ' ' + c.City +', ' + IsNull(c.StateOrRegion+ ', ', '')+ c.Country+', ' + c.PostalCode AS 'ADDRESS'
From (Suppliers AS s LEFT JOIN Products AS p
on s.SupplierID = p.SupplierID) LEFT JOIN 
(OrderDetails AS od ON p.ProductID = od.ProductID) LEFT JOIN (Orders AS o 
on od.OrderID = o.OrderID) LEFT JOIN (Customers AS c 
on o.CustomerID = c.CustomerID)
    Where Discontinued = 1
    Order By p.ProductName

Error is: "Syntax error in JOIN operation", with the highlighted 'on' in all caps for your viewing convenience.

Upvotes: 1

Views: 1243

Answers (1)

Firestar001
Firestar001

Reputation: 57

So here's the final code:

Select  p.ProductName AS 'Out of Stock Product', o.OrderID, c.CompanyName, c.ContactName, c.Address, c.City , c.StateOrRegion, c.Country, c.PostalCode
From 
(((
(Suppliers AS s LEFT JOIN Products AS p
on s.SupplierID = p.SupplierID) LEFT JOIN 
OrderDetails AS od
on p.ProductID = od.ProductID) LEFT JOIN Orders AS o 
on od.OrderID = o.OrderID) LEFT JOIN Customers AS c 
on o.CustomerID = c.CustomerID)
    Where Discontinued = 1
    Order By p.ProductName

I had to add parenthesis 'pulling in' the other join items. And in access I can't link multiple columns as one thing. Derp de dur

Upvotes: 1

Related Questions