Combined queries fail when one had no data

  • Thread starter Thread starter George
  • Start date Start date
G

George

I'm using two queries as the basis for a third query and
for a report that sums fields for a specific year
selected by the user. When one of the queries has no data
for the year selected, the third query produces no data,
even though the other base query has data.
How can I convert the no-data query to zeros, or
otherwise solve the problem?
Thanks for your assistance.
 
Hi,

If your using a crosstab you can use Nz(value,0) on the value, and use fixed column names that show up regardless.

Otherwise, its more difficult. I'll sketch out how I did it
1) Create table of integers tInteger, one field I. Add records 0,1,2,3..12
2) create a query of the months your looking for

SELECT DateAdd("m",-,bom(Date())) AS dtmBOM
FROM tInteger
WHERE (((tInteger.I) Between 0 And 11));

BOM(dtm) is dateserial(year(dtm),month(dtm),1)

3) sum your data by month. One field will be dtmBOM using BOM above
4) add both queries 2), 3) to a new query. Left join from months to your data on dtmBOM. Sum your value with NZ(), something like

SELECT [_q2].dtmBOM, Sum(Nz(.[TOT],0)) AS Tot
FROM _q2 LEFT JOIN _q3 AS B ON [_q2].dtmBOM = B.dtmBOM
GROUP BY [_q2].dtmBOM;

where _q2 is 2)

(you could also add a column Tot: 0 to 2), then union query the months and your data and Tot. All the 'blank' months will be 0)

Good luck, Peter.
 
Thanks very much for the suggestion, Peter. I'll see if I
can use this approach.
George
-----Original Message-----
Hi,

If your using a crosstab you can use Nz(value,0) on the
Click to expand...
value, and use fixed column names that show up regardless.
Otherwise, its more difficult. I'll sketch out how I did it
1) Create table of integers tInteger, one field I. Add records 0,1,2,3..12
2) create a query of the months your looking for

SELECT DateAdd("m",-,bom(Date())) AS dtmBOM
FROM tInteger
WHERE (((tInteger.I) Between 0 And 11));

BOM(dtm) is dateserial(year(dtm),month(dtm),1)

3) sum your data by month. One field will be dtmBOM using BOM above
4) add both queries 2), 3) to a new query. Left join
Click to expand...

from months to your data on dtmBOM. Sum your value with
NZ(), something like
SELECT [_q2].dtmBOM, Sum(Nz(.[TOT],0)) AS Tot
FROM _q2 LEFT JOIN _q3 AS B ON [_q2].dtmBOM = B.dtmBOM
GROUP BY [_q2].dtmBOM;

where _q2 is 2)

(you could also add a column Tot: 0 to 2), then union
Click to expand...

query the months and your data and Tot. All the 'blank'
months will be 0)
 
Back
Top