Error running a query in MS Access database

Question

select 
    tblproduct.itemname As Item_Name, 
    ((sum(tblpurchase.[case]) * tblproduct.pc)-
    ((sum(tblsale.[case]) * tblproduct.pc)+(sum(tblsale.nos)))) 
As Remaining_Stock
From 
    (tblproduct 
left Join 
    tblpurchase on tblproduct.itemname=tblpurchase.itemname) 
left join 
    tblsale on tblproduct.itemname = tblsale.itemname 
where 
    tblproduct.itemname='Combo Soap' 
group by 
    tblproduct.itemname

Error is shown in the screenshot. I am unable to run this query. Please help me to find solution how to solve and run this query! Thank you.

Answer 1

Try this:

select 
    tblproduct.itemname As Item_Name, 
    sum(tblpurchase.[case] * tblproduct.pc) -
    sum(tblsale.[case] * tblproduct.pc) +
    sum(tblsale.nos) As Remaining_Stock
from

<snip>

Output:

Answer 2

My thanks to all of you who helped me move forward, so I could solve this query.

SELECT tblproduct.itemname,
       ( 
        Sum(tblpurchase_grouped.total_case) * Sum(tblproduct.pc) + 
        Sum(tblpurchase_grouped.total_nos)
       ) - 
       ( 
        Sum(tblsale_grouped.total_case) * Sum(tblproduct.pc) +
        Sum(tblsale_grouped.total_nos)
       ) AS Total
FROM (tblproduct
LEFT JOIN (
   SELECT itemname,
          SUM(nos) AS total_nos,
          SUM([case]) AS total_case
   FROM tblsale
   GROUP BY itemname
) AS tblsale_grouped
  ON tblproduct.itemname = tblsale_grouped.itemname)
LEFT JOIN (
   SELECT itemname,
          SUM(nos) AS total_nos,
          SUM([case]) AS total_case
   FROM tblpurchase
   GROUP BY itemname
) AS tblpurchase_grouped 
   ON tblproduct.itemname = tblpurchase_grouped.itemname
WHERE tblproduct.itemname='Maza'
GROUP BY tblproduct.itemnam