MS Access Query Syntax Error in FROM

Question

This is for an assignment...

I am to

Write SQL out to display Customer's Last name, order date, product ID, fabric of the product, quantity ordered, and unit price. This must be a 4-table join and must user INNERJOIN even if where can be used.

These are the tables involved.

Cus (CID, Last, First, Phone)
Orders (OrdID, OrdDate, ShipDate, CID)
Manu (ManuID, Name, Phone, City)
Prods (PrID, ManuID, Category)
ProdFabric (PrID, Fabric, Price)
Orderline (OrdId, PrID, Fabric, Qty)

Bold means it is a primary key or part of a composite key.

Here is the query I entered, but I keep getting a Syntax error on FROM and it keeps highlighting Orders (after Cus INNERJOIN) for some reason.

SELECT Last, OrdDate, Prods.PrID, Fabric, Qty, Price  
FROM Cus INNERJOIN Orders INNERJOIN Orderline INNERJOIN ProdFabric   
ON OrderLine.PrID=ProdFabric.PrID  
AND ON Orderline.Fabric=ProdFrabric.Fabric  
AND ON Cus.CID=Orders.CID  
AND ON Orders.OrdID=Orderline.OrdID;

Answer 1

I don't think "AND ON" exists like you use it in your query.
You have to specify each join on its own.

SELECT ...
FROM cus INNER JOIN orders ON cus.cid = orders.cid
         INNER JOIN orderline ON orders.orderid = orderline.orderid
...

or you can also specify the join in your WHERE clause:

SELECT ...
FROM cus INNER JOIN orders 
         INNER JOIN orderline
...
WHERE cus.cid = orders.cid
  AND orders.orderid = orderline.orderid
...

Also: INNER JOIN are 2 words ;-)

Answer 2

AFAIK MS Access requires a Lisp-like proliferation of parentheses to support multiple joins.

• Edit your MS Access queries in another editor that supports paren-matching and syntax-highlighting, e.g. Notepad++.
• Use table aliases to keep your queries shorter and more manageable.

With the parentheses, your query will look something like this:

SELECT c.Last
       , o.OrdDate
       , pf.PrID
       , pf.Fabric
       , ol.Qty
       , pf.Price
FROM   ((Cus c
       INNER JOIN Orders o ON c.CID = o.CID)
       INNER JOIN Orderline ol ON o.OrdID = ol.OrdID)
       INNER JOIN ProdFabric pf ON ol.PrID = pf.PrID;