Forcing a query to return a value instead of an empty recordset

[irishandy]

Is this possible?

I've got a report whose source is a master query based on a number of subqueries. If any of these queries returns an empty recordset the master query also returns an empty recordset. In cases like this I'd like the subquery to instead be forced to return the value "0" (zero) so that the report could run.

Any help on this would be appreciated as it's driving me nuts.

 

[Golom]

You probably need LEFT or RIGHT joins but, without seeing your SQL, it's difficult to be more specific.

 

[irishandy]

There are actually no joins in the query at all. I added all the queries to the master query and selected specific fields from each one. Each subquery was designed to only return one record containing two fields, one being a count of a certain subset of records and the other being the sum of the value contained in those records. There are 17 such subqueries.

The master query (and the resulting report) has always worked fine as it was never anticipated that one of the subsets would ever be zero. Now that it has happened, the query has fallen over.