MS Access HW Query Missing Obvious Error Somewhere?

Question

Final query that I have to do and I'm getting an error stating that I have not specified OrdID's table:

Here's what I have:

SELECT Last, OrderLine.OrdID, OrdDate, SUM(Price*Qty)
FROM ((Cus INNER JOIN Orders ON Cus.CID=Orders.CID) INNER JOIN OrderLine ON Orders.OrdID=OrderLine.OrdID) INNER JOIN ProdFabric ON OrderLine.PrID=ProdFabric.PrID
AND OrderLine.Fabric=ProdFabric.Fabric
GROUP BY Last
ORDER BY Last DESC, OrdID DESC;

When I hit run, it keeps saying that OrdID could refer to more than one table listed in the FROM clause.

Why does it keep saying that as I've specified which table to select for OrdID.

Tables:

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)

Answer 1

The ORDER BY clause is agnostic to what you've specified in the SELECT list. Its possible for example to order by a field that you don't actually include in the output select list.

Hence you need to be sure that the fields in the Order by list are not ambigious.

Answer 2

Your ORDER BY clause is valid SQL-92 syntax.

Sadly, the Access database engine is not SQL-92 compliant. It doesn't let you use a column correlation name ('alias') from the SELECT clause. If you had used this:

SUM(Price * Qty) AS total_price
...
ORDER BY total_price

you would have received an error. (Aside: you should consider giving this expression a column correlation name anyhow.)

Instead of correlation names, the Access data engine is expecting either a column name or an expression (the latter being illegal in SQL-92); specified columns need not appear in the SELECT clause (again illegal in SQL-92). Because any column from any table in the FROM clause can be used, you need to disambiguate them with the table name; if you used a table correlation name in the FROM clause then you must use it in the ORDER BY clause (I don't make the rules!)

To satisfy the Access database engine's requirements, I think you need to change your ORDER BY clause to this:

ORDER BY Last DESC, OrderLine.OrdID DESC;

As an aside, I think your code would be more readable if you qualify you columns with table names in your SELECT clause even when they are unambiguous in context (I find using full table names a little wordy and prefer short table correlation names, specified in the data dictionary and used consistently in all queries). As it stands I can only guess that OrdDate is from Orders, and Price and Qty are from OrderLine. I've not idea what Last represents.