Null adding difficulty

[gsgriffin]

Rather than trying to use words, below is an example of my problem.

Query 1 Results:
Column 1 Column 2 Column 3 Column 4
====================================================
a 15 c 10 a 25 b 10
c 5 null null b 15 null null
null null b 15 c 5 a 15
a 10 null null null null null null

Each variable above (letter) corresponds with the cell after it that has a number. I want to sum each variable for each value in every column and row. I perform queries that group and sum by columns producing

Column 1 Query:
a 25
b null
c 5

Column 2 Query:
a null
c 10
b 15

Column 3 Query:
a 25
b 15
c 5

I would now like to reduce my results down to the the sum of all of these to have

Total Query
a 50
b 30
c 10

My result is obviously
a null
b null
c 10

The problem comes that when I add together the queries, if there are null values, the sum is null. Is there any way to have a query substitue a zero value if a null is found or am I going about this the wrong way?

 

[WildHare]

Check out the NZ() function

Null + 17 = Null
NZ(Null) + 17 = 17

Sets any NULLS to zero for computational purposes.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[gsgriffin]

YOU'RE THE MAN!!!!!!

That was the function I was looking for but didn't know about.

 

[gsgriffin]

Hey...I was trying to solve another problem of mine and came across SQL Union function. I did the following:

SELECT ALL [Finan1ID], [Amount1]
from FinanceBaseQuery

UNION ALL SELECT [Finan2ID], [Amount2]
from FinanceBaseQuery

UNION ALL SELECT [Finan3ID], [Amount3]
from FinanceBaseQuery

UNION ALL SELECT [Finan4ID], [Amount4]
from FinanceBaseQuery

UNION ALL SELECT [Finan5ID], [Amount5]
from FinanceBaseQuery

UNION ALL SELECT [Finan6ID], [Amount6]
from FinanceBaseQuery

UNION ALL SELECT [Finan7ID], [Amount7]
from FinanceBaseQuery

UNION ALL SELECT [Finan8ID], [Amount8]
from FinanceBaseQuery

UNION ALL SELECT "Deposits", [SumOfDeposit]
from Deposits;

This put all my data into two columns, one after another. Now all my data looked like:

a 20
null null
b 15
a 5
null null
c 5
etc..

From here it was a simple group by and sum to get the totals. Is this a good method too?