No results in a summing query

[rirus via AccessMonster.com]

Here is the SQL for the query:

SELECT Defect1_Total.[Def1_Count], Defect2_Total.[Def2_Count], Defect3_Total.
[Def3_Count], Defect4_Total.[Def4_Count], [Def1_Count]+[Def2_Count]+
[Def3_Count]+[Def4_Count] AS [Total Defects]
FROM Defect1_Total, Defect2_Total, Defect3_Total, Defect4_Total
GROUP BY Defect1_Total.[Def1_Count], Defect2_Total.[Def2_Count],
Defect3_Total.[Def3_Count], Defect4_Total.[Def4_Count]];


Defect1_Total, Defect2_Total, Defect3_Total and Defect4_Total are queries
each containing a defect count. I have a 5th column which contains the sum of
the other 4 columns. So my output would be:

Def1_Count Def2_Count Def3_Count Def4_Count Total Defects
2 1 0 5
8

This query works as long as there is data in the counts. My problem is, if
there is no data in one of the Counts, meaning the original query received no
data because the table had no data in it, then it shows up blank and my Total
Defects will be blank.

Def1_Count Def2_Count Def3_Count Def4_Count Total Defects
2 1 0

How can I get Def4_Count to display a 0 when there is not data and the actual
Def4_Count data when there is data so that the Total Defects will give me a
result. I have tried, iif(Def4_Count = "" ,"0",Def4_Count) and different
variations to get the 0 or the actual number if there is data. Nothing seemed
to work

I hope this is not too confusing. Thank you for any ideas or suggestions.

rirus

 

[NuBie via AccessMonster.com]

use Nz() function, i tried this one before.. i.e. Nz(MyField, 0) in your
case

SELECT nz(Defect1_Total.[Def1_Count],0), nz(Defect2_Total.[Def2_Count],0), . .
.etc.

Here is the SQL for the query:

SELECT Defect1_Total.[Def1_Count], Defect2_Total.[Def2_Count], Defect3_Total.
[Def3_Count], Defect4_Total.[Def4_Count], [Def1_Count]+[Def2_Count]+
[Def3_Count]+[Def4_Count] AS [Total Defects]
FROM Defect1_Total, Defect2_Total, Defect3_Total, Defect4_Total
GROUP BY Defect1_Total.[Def1_Count], Defect2_Total.[Def2_Count],
Defect3_Total.[Def3_Count], Defect4_Total.[Def4_Count]];


Defect1_Total, Defect2_Total, Defect3_Total and Defect4_Total are queries
each containing a defect count. I have a 5th column which contains the sum of
the other 4 columns. So my output would be:

Def1_Count Def2_Count Def3_Count Def4_Count Total Defects
2 1 0 5
8

This query works as long as there is data in the counts. My problem is, if
there is no data in one of the Counts, meaning the original query received no
data because the table had no data in it, then it shows up blank and my Total
Defects will be blank.

Def1_Count Def2_Count Def3_Count Def4_Count Total Defects
2 1 0

How can I get Def4_Count to display a 0 when there is not data and the actual
Def4_Count data when there is data so that the Total Defects will give me a
result. I have tried, iif(Def4_Count = "" ,"0",Def4_Count) and different
variations to get the 0 or the actual number if there is data. Nothing seemed
to work

I hope this is not too confusing. Thank you for any ideas or suggestions.

rirus