Performing Left Join

[williey]

Hi,
I did a 2 table join. The Total column is the result of the sum of Column1 and Column2. When there is no match, either column1 or column2 will return empty. How do I get it to return 0? The example shows the Total column will be empty if either Column1 or Column2 is empty.

EmpNo	Reg Hours	Column1	      Column2	Total
01673	   536	          375		
02018	   518		                269	
02030	   544		                835	
02033	   472		                362	
04271	   526	          567		
04771	   404		                395	
04785	   504	          364		
04822	   504	          110		
04898	   548		               2899	
04971	   498		                135	
05058	   305	          519	  1000     1519

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[Golom]

Check out the NZ function. The empty columns are probably NULL and NULL + anything is NULL.

 

[PHV]

Have a look at the Nz function:
SELECT ..., Sum(Nz(Column1,0)+Nz(Column2,0)) AS Total, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[traingamer]

You could use the NZ() function.

nz([Column1], 0)

or Nz([Column1])

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

 

[williey]

Thanks guys for your help!

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.