sql - Left Join on three tables in access with Group by

Question

I have broken my head with syntax error response from Access Jet engine. I have three tables.

First one "tblMstItem" is the master table for Item details contains two columns "colITemID" PK and "colItemName"

Second one "tblStocks" is the table where the purchases are maintained. This table has a column "colRQty" which keeps the quantity of the particular item purchased. "colItemID" FK from "tblMstItem"

Third one "tblSales" is the table where the sales are maintained. This table has a column "colSoldQty" which keeps the quantity of the particular item sold. "colItemID" FK from "tblMstItem"

Therefore "colItemID" is common in all the three tables and has links created.

My requirement is I need all the Items listed in the "tblMstItem" table columns are "colItemID" "colItemName" and if there is any item purchased or any item sold should be shown as sum of that particular item.

I have used Left Join shown in the following select statement but it always giving me an error message.

Select statement as follows:

SELECT 
      i.colItemID, 
      i.colItemName, 
      s.rqty, 
      n.soldqty 
from tblMstItem i
left join 
         ( select sum( colRQty ) as rqty from tblStocks group by colItemID ) s 
on i.colItemID = s.colItemID
left join 
         ( select sum( colSoldQty ) as soldqty from tblSales group by colItemID ) n 
on i.colItemID=n.colItemID``

I tried the above given code with many different syntax but every time I get syntax error. It is making me to doubt do MS Access support three table joins, I am sure I am wrong.

See the error Message below

Table columns and table link shown below

I would be very thankful to get any help on this. Access sql please because this I am able to get results in SQL Server.

Thanks in Advance

标签： sqlms-accessgroup-byleft-joinmultiple-columns

Answer 1

MS Access has a picky syntax. For instance, joins need extra parentheses. So, try this:

select i.colItemID, i.colItemName, 
       s.rqty, n.soldqty 
from (tblMstItem as i left join 
      (select colItemID, sum(colRQty ) as rqty
       from tblStocks
       group by colItemID
      ) as s 
      on i.colItemID = s.colItemID
     ) left join 
     (select colItemID, sum( colSoldQty ) as soldqty
      from tblSales
      group by colItemID
     ) as n 
     on i.colItemID = n.colItemID;

You also need to select colItemID in the subqueries.