SQL query to perform in access database

Question

Table:

purchase_stock_entry(id,purchase_party_id,bill_no,dt,product_id,qty,price,type)
purchase_party_name(purchase_party_id,name_shop)

I want output in column as (bill_no,name_shop,sum(qty*price)) with join of this two table

i am also tries:

select p1.bill_no,p2.name_shop,p1.qty*p1.price as [Total],p1.dt from purchase_stock_entry
p1,purchase_party_name p2 where p1.purchase_party_id=p2.id

but for the sum how i can??

Answer 1

Please use this, It can be work

SELECT p1.billno, p2.name_shop, sum(p1.price * p1.qty) FROM purchase_stock_entry p1, purchase_party_name p2 WHERE p1.purchase_party_id = p2.purchase_party_id

Answer 2

SELECT bill_no, name_shop, SUM(qty * price) AS [total]
FROM purchase_stock_entry INNER JOIN purchase_party_name
  ON purchase_stock_entry.purchase_party_id = purchase_party_name.purchase_party_id
GROUP BY bill_no, name_shop;