Null result

[Guest]

Overview: I have 3 queries searching 3 different tables for 2 fields. I then
have one combining query that adds the results of the 2 fields from the 3
queries.

Each query finds records in the table that match a particular date. When
the records are found each query returns the sum of the 2 fields in table.
If one of the tables doesn't have any records that match the date, the
combining query is blank, it doesn’t return the sum from other 2 queries .
What I want to do is somewhere in the query to say that if no record is found
then return zero in the fields. This formular is giving me some problems.
Help Please.

 

[Michel Walsh]

SELECT a.f1, Nz(b.f2, 0) + Nz(c.f2, 0)
FROM (a LEFT JOIN b ON a.f1=b.f1)
LEFT JOIN c ON a.f1=c.f1


with tables a, b, and c, 'matching' field, f1, and to be summed fields, f2.

If you use MS SQL Server instead of Jet, substitute Nz with COALESCE.


Hoping it may help,
Vanderghast, Access MVP