Returning an Access recordset with zeros instead of nulls

Here's the problem:

I have an Access query that feeds a report, which sometimes doesn't return any records for certain criteria. I would like to display zeros in the report instead of an empty line (an empty recordset is currently being returned). Is there an SQL solution that (perhaps using some kind of union st开发者_开发问答atement and/or nested SQL) always returns one record (with zeros) if there are not matching records from the initial query?

---

One possible solution would be to create a second table with the same primary key, and add just one record. In your query, choose as join type all records in the second table, including those with no matching records in the first one. Select as output all fields in the first table.

---

You can materialize a one-row table with zero for all columns. This is a slight pain to achieve in Access (ACE, Jet, whatever) because it doesn't support row constructors and the FROM must resolve to a base table. In other words, you'll need a table that is guaranteed to always contain at least one row.

This isn't a problem for me because my databases always include auxilliary tables e.g. a calendar table, a sequence table of integers, etc. For exmaple, to materialize a table one-row, all-zeros table using my 3000 row Calendar table:

SELECT DISTINCT 0 AS c
  FROM Calendar;

I can then UNION my query with my materialized table but include an antijoin to ensure the all-zeros row only appears in the resultset when my query is the empty set:

SELECT c 
  FROM T       
UNION 
SELECT 0
  FROM Calendar
 WHERE NOT EXISTS (
                   SELECT c 
                     FROM T
                  );

Note the use of UNION allows me to remove the DISTINCT keyword and the AS clause ("column alias") from the materialized table.