Select query can not add up output of 2 crosstab queries 2

[GaborH]

Please help. This is likely something very basic, but I can’t see past it.

Three separate cross tab queries list allocate revenues to different departments. A separate query is used for each month. Ideally, I am trying to combine all three queries via a select query. What is stumping me, is that on the select query, I can not seem to add the revenues that are calculated in each separate query. The combination [Rev1]+ [Rev2]+[Rev3] only yields values where there are numbers for all three columns. In rows where there is no value for either factor, the equation yields a blank.

All factors are numbers. I have also tried a version where each cross-tab query is made into a separate table, and then using a select query. The result was the same.

All hints are highly appreciated. Thank you

 

[jksmi]

Hi, use a union query to stack your 3 queries and then do a select query on that. Union query would look something like

SELECT Query1.*
FROM Query1
UNION
SELECT Query2.*
FROM Query2;

You'll then be able to use aggregate functions HTH, Jamie

 

[Golom]

In general "Number + NULL = NULL". You need to convert your NULL values to something else to get a numeric result. Try this
[blue][tt]
NZ([Rev1]) + NZ([Rev2]) + NZ([Rev3])
[/tt][/blue]