Query works w/ Access, not with SQL Server

[nestoc]

I have run into a frustrating problem I can't make any sense of. I have a query that joins 3 tables. It works fine when the first table is an Access table, but throws an error message when I change the first table to a linked table (SQL Server). The second two tables are always linked tables and there is no problem. The odd part is the error message only references the third table, which I am not changing at all. Can anyone shed some light on this?

QUERY:
Works perfectly when ORDERS is a local table, and INVENTORY and ORDLINE are linked tables. Breaks as soon as I convert ORDERS to linked table.

SELECT INVENTORY.item_no, Sum(ORDLINE.qty_to_ship) AS num_sold

FROM (dbo_oehdrhst_sql AS ORDERS LEFT JOIN dbo_oelinhst_sql AS ORDLINE ON (ORDERS.ord_no = ORDLINE.ord_no) AND (ORDERS.ord_type = ORDLINE.ord_type) AND (ORDERS.inv_no = ORDLINE.inv_no)) 

LEFT JOIN dbo_iminvloc_sql AS INVENTORY ON (ORDLINE.item_no = INVENTORY.item_no) AND (ORDLINE.loc = INVENTORY.loc)

WHERE ORDERS.inv_dt >= DateSerial(Year(Date())-1,Month(Date()),Day(Date())) And ORDERS.inv_dt <= Date() AND INVENTORY.prod_cat="PTS"

GROUP BY INVENTORY.item_no;

ERROR MSG (only references INVENTORY table which is not changing):

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "INVENTORY.prod_cat" could not be bound. (#4104)
[Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "INVENTORY.loc" could not be bound. (#4104)
[Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "INVENTORY.item_no" could not be bound. (#4104)

 

[SQL_Hell]

Please show the t-sql create statement of all 3 tables

 

[nestoc]

How would I find that? The database these tables are in is not mine, it's a piece of software my company uses for inventory etc. I am just linking to some of the tables to reference inventory levels and whatnot in some reports/forms that I use which are separate from the main program. I had saved these 3 as local tables in order to create my report, and when I finished and re-linked them it broke.

 

[SQL_Hell]

ok, if you can't do that then can you show how the tables look in linked table manager?

SQL server tables are always designated with a schema, like so:

SchemaName.tableName

Such as:

dbo.Customers

Maybe you are missing the schema?

 

[nestoc]

Ok so I actually figured out what the problem was. I guess I had the tables joined in the wrong order.

This was my original query (shortened/substituted stuff for readability):

SELECT xxx
FROM (A LEFT JOIN B ON A.xxx = B.xxx)
LEFT JOIN C ON B.xxx = C.xxx
WHERE xxx

When I switched to this it worked:

SELECT xxx
FROM (A LEFT JOIN B ON A.xxx = B.xxx)
LEFT JOIN C ON A.xxx = C.xxx
WHERE xxx

All of the nested ON clauses in the join appear to have to reference the first table and not one of the subsequently joined tables, at least that's how I'm understanding it. I just didn't think there was a problem with the query at first because it was working fine with the local table.

So problem solved.. but any insight as to why this worked with Access and not with SQLserver would be welcome.