Using Query to sum up

[Simon]

I have a table that has products i have bought and also have a table of
products

I want to do a query that will total up all quantity of each product i
have bought

I can do this in in query but in the resuults it will not include the
products that have not had any stock bought

How do i get it to dispay each product with the quantity bought and if
nothing has been bought then it just displays 0


Thanks

 

['69 Camaro]

Hi, Simon.

In this example, both tables have ProdID as a column. It's the primary key of
tblProducts and the foreign key in tblProductsBought. Try:

SELECT tblProducts.ProdID, tblProductsBought.ProdName,
IIF(ISNULL(SUM(tblProductsBought.NumBought)), 0,
SUM(tblProductsBought.NumBought)) AS Total
FROM tblProducts LEFT JOIN tblProductsBought
ON tblProducts.ProdID = tblProductsBought.ProdID
GROUP BY tblProducts.ProdID, tblProductsBought.ProdName

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.