Null Values in Join Query 1

[kentwoodjean]

I have a query where I joined like fields and it works perfectly. It contains 2 columns of numbers by date, although there are some dates where one or the other column have Null values. I have added a 3rd colum that totals the other 2 columns. Currently it only shows a grand total when there is an entry in both columns. If one of them is Null, there is no grand total. What else should I put in the SQL below so I will always have a grand total for each date listed. Tried using what I thought would help from a previous query I received help on but it didn't work.

SELECT q.RECDT AS RECDT, Sum(q.CountOfRECDT) AS TotREC, Sum(q2.CountOfRECDT) AS REC2, [TotREC]+[REC2] AS Total
FROM [Countof RecvdNasco] AS q LEFT JOIN [Query1 CloseRec] AS q2 ON q.RECDT = q2.RECDT
GROUP BY q.RECDT;

 

[PHV]

Have you tried this ?
SELECT q.RECDT AS RECDT, Sum(Nz(q.CountOfRECDT,0)) AS TotREC, Sum(Nz(q2.CountOfRECDT,0)) AS REC2, [TotREC]+[REC2] AS Total
FROM [Countof RecvdNasco] AS q LEFT JOIN [Query1 CloseRec] AS q2 ON q.RECDT = q2.RECDT
GROUP BY q.RECDT;


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

 

[kentwoodjean]

That does it! I always forget about the Nz. I guess that is because I rarely have a need to use it. Thanks for reminding me of this important element.