URGENT: Handling Null in Sum of integer field

[Abhi24]

Hi,

I have a table in access 2007 which contains 3 columns - Model number, date, quantity(numeric field).
There is another list of model numbers and the sum of quantity for each of these model numbers has to be calculated between 2 given dates.
The problem is if the model number does not appear in the table, my query returns blank.



I tried ISNULL(SUM(Quantity)) , SUM(Quantity) IS NULL and SUM(NZ(Quantity)) but nothing is working. I keep getting blank as answer.

I need to complete this project on an urgent basis. Please help.

[Perceptus]

Are you using an Aggregate query?

[Abhi24]

Let me tell you exactly what I am doing here..
There are 2 tables -
1) Sales : having fields - Material, Cdate, Quantity
2) Goods : having fields - Code, Stock
[Code] is same as [Material]. But there can be more values of [Code] in FG compared to [Material] in Sales. So, all the [Material] values in Sales will map to [Code] in Goods table

This is the original query:

[CODE]SELECT (SUM(t1.[Quantity]) + (Select Sum([Stock]) From [Goods] where

 = t1.Material)) AS Quantity FROM [Sales] AS t1 WHERE [Cdate] <=  #11/01/2016# AND [Cdate] >  #11/01/2015# GROUP BY Material;

The problem is SUM(t1.[Quantity]) delivers NULL value in some cases which makes this whole query return 0 instead of (0+ Sum[Stock])

[Abhi24]

Sorry for the above error in posting..

Let me tell you exactly what I am doing here..
There are 2 tables -
1) Sales : having fields - Material, Cdate, Quantity
2) Goods : having fields - Code, Stock
[Code] is same as [Material]. But there can be more values of [Code] in Goods compared to [Material] in Sales. So, all the [Material] values in Sales will map to [Code] in Goods table

This is the original query:

SELECT (SUM(t1.[Quantity]) + (Select Sum([Stock]) From [Goods] where [Code] = t1.Material)) AS Quantity FROM [Sales] AS t1 WHERE [Cdate] <= #11/01/2016# AND [Cdate] > #11/01/2015# GROUP BY Material;

The problem is SUM(t1.[Quantity]) delivers NULL value in some cases which makes this whole query return 0 instead of (0+ Sum[Stock])

[Perceptus]

Try
SELECT SUM(t1.[Quantity]) as exp1 (Select Sum([Stock]) From [Goods] where [Code] = t1.Material)) AS exp2, [exp1]+[exp2] as Exp3 FROM [Sales] AS t1 WHERE [Cdate] <= #11/01/2016# AND [Cdate] > #11/01/2015# GROUP BY Material;

NOt tested. but in theory should add the values and provide them in Exp3.

[Abhi24]

I tried but it did not work. All 3 columns are blank. Any other ideas?

[Perceptus]

have you tried joining the two tables via Inner Join with Material = Code?

[Abhi24]

yes I did with no luck

[ssanfu]

Maybe use the NZ() function

SELECT (SUM(NZ(t1.[Quantity],0)) +  (Select Sum([Stock]) From [Goods] where  [C ode] = t1.Material)) AS Quantity FROM [Sales] AS t1 
WHERE [Cdate]  <=  #11/01/2016# AND [Cdate] >  #11/01/2015# GROUP BY Material;

[Abhi24]

I had tried this earlier but it did not solve the issue. I guess one way out is to create another table Sales1 which has all the [Code] values in [Material] with 0 as quantity for the extra ones. Then I will rerun the query replacing Sales with Sales1.