Results 1 to 2 of 2
Thread: Sum in a query (Access 2000)
2002-12-30, 20:32 #1
- Join Date
- Sep 2002
- Thanked 0 Times in 0 Posts
Sum in a query (Access 2000)
I have a query called StockFlow that derives its values from three queries.However if one of the queries is blank, then then query
StockFlow is also not populated.Is there any possibility to use mine query StockFlow when one of the queries is blank?
My query is the following :
SELECT qryOnOrder.Productid, qryOnOrder.grade, qryOnOrder.size, Sum(qryOnOrder.SumOfcartons) AS OnOrder, Sum(qrySold.SumOfcartons) AS Sold,
Sum(qryInflow.SumOfcartons) AS Inflow
FROM (qryOnOrder INNER JOIN qrySold ON qryOnOrder.Productid = qrySold.Productid) INNER JOIN qryInflow ON qrySold.Productid = qryInflow.Productid
GROUP BY qryOnOrder.Productid, qryOnOrder.grade, qryOnOrder.size;
2002-12-30, 23:23 #2
- Join Date
- Jun 2002
- Mt Macedon, Victoria, Australia
- Thanked 45 Times in 44 Posts
Re: Sum in a query (Access 2000)
"Inner Joins" return records only where fileds match on both sides. You need to replace these by left or right joins.
If you work with the query grid, then double click the join line, and you will see the dialog box shown below.
You will also need to add to some NZ functions to replace blanks field by zeros.Regards