Returning 0 for null values.

[Guest]

Here is an easy one for you guys. How can I modify this query to return
zeros for any null field that may be returned in this query. I know that it
will probably be an Iff statement or Nz, but I cannot seem to find an example
of this type of right, left, union query, with replacing the resulting null
fields with zeros. I know that I could write another query, but I really
need it to be only one query. Any help would be greatly appreciated.

SELECT A.Category, A.Amount AS AmountA, B.Amount As AmountB
FROM Table1 AS A RIGHT JOIN Table2 AS B
ON A.Category = B.Category
UNION
B.Category, A.Amount AS AmountA, B.Amount As AmountB
FROM Table1 AS A LEFT JOIN Table2 AS B
ON A.Category = B.Category

 

[Eileen]

For any numeric field if you use the nz statement that will work.
For example

SELECT nz([a.category],0), nz([a.amount],0) etc.........

Hope that helps.

Thanks
Eileen

 

[Douglas J. Steele]

SELECT B.Category, A.Nz(Amount, 0) AS AmountA, B.Amount As AmountB
FROM Table1 AS A RIGHT JOIN Table2 AS B
ON A.Category = B.Category
UNION
A.Category, A.Amount AS AmountA, Nz(B.Amount, 0) As AmountB
FROM Table1 AS A LEFT JOIN Table2 AS B
ON A.Category = B.Category

Note that I change which field is shown as the first field in the query as
well.

If you're using A RIGHT JOIN B, that will give you everything in B, whether
or not there's a matching entry in A. That means you want B.Category
displayed. Similarly, A LEFT JOIN B gives you everything in A, whether or
not there's a matching entry in B, so you want A.Category.

 

[Guest]

Thanks for your input Eileen and Doug, but I could not get either suggestion
to work. I was hoping that using the simple example before would make it
easier to see the format. Anyway, here is the full sql code that I am
working with:

SELECT BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP, Sum(BOLT.PARVALUE) AS
SumOfPARVALUE, Sum(BOLT.PRINCOST) AS SumOfPRINCOST, Sum(BOLT.BOOKVALUE) AS
SumOfBOOKVALUE, Sum(InTrader.PARVALUE) AS SumOfPARVALUE1,
Sum(InTrader.PRINCOST) AS SumOfPRINCOST1, Sum(InTrader.BOOKVALUE) AS
SumOfBOOKVALUE1
FROM BOLT RIGHT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND
(BOLT.BDSKACCT = InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP
ORDER BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP
UNION SELECT BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP, Sum(BOLT.PARVALUE) AS
SumOfPARVALUE, Sum(BOLT.PRINCOST) AS SumOfPRINCOST, Sum(BOLT.BOOKVALUE) AS
SumOfBOOKVALUE, Sum(InTrader.PARVALUE) AS SumOfPARVALUE1,
Sum(InTrader.PRINCOST) AS SumOfPRINCOST1, Sum(InTrader.BOOKVALUE) AS
SumOfBOOKVALUE1
FROM BOLT LEFT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND (BOLT.BDSKACCT
= InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP
ORDER BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP;

I need to have zeros returned for any field that is null except for each of
the two bdskacct, grp, and cust fields. These will require a IIf statement
with a "Not found" put in the Null Fields. Hopefully, I can handle that
one.

Anyway, if either of your guys could help me with this big mess, I would
greatly appreciate it.

Thanks,
Phillip

For any numeric field if you use the nz statement that will work.
For example

SELECT nz([a.category],0), nz([a.amount],0) etc.........

Hope that helps.

Thanks
Eileen

Here is an easy one for you guys. How can I modify this query to return
zeros for any null field that may be returned in this query. I know that
it
will probably be an Iff statement or Nz, but I cannot seem to find an
example
of this type of right, left, union query, with replacing the resulting
null
fields with zeros. I know that I could write another query, but I really
need it to be only one query. Any help would be greatly appreciated.

SELECT A.Category, A.Amount AS AmountA, B.Amount As AmountB
FROM Table1 AS A RIGHT JOIN Table2 AS B
ON A.Category = B.Category
UNION
B.Category, A.Amount AS AmountA, B.Amount As AmountB
FROM Table1 AS A LEFT JOIN Table2 AS B
ON A.Category = B.Category

 

[Douglas J. Steele]

As I pointed out earlier, you seem to have things backwards. Your first
subquery:

SELECT BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP, Sum(BOLT.PARVALUE) AS
SumOfPARVALUE, Sum(BOLT.PRINCOST) AS SumOfPRINCOST, Sum(BOLT.BOOKVALUE) AS
SumOfBOOKVALUE, Sum(InTrader.PARVALUE) AS SumOfPARVALUE1,
Sum(InTrader.PRINCOST) AS SumOfPRINCOST1, Sum(InTrader.BOOKVALUE) AS
SumOfBOOKVALUE1
FROM BOLT RIGHT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND
(BOLT.BDSKACCT = InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP
ORDER BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP

has a right join between BOLT and InTrader. That means that you'll get a row
for every entry in InTrader, whether or not there's a matching row in BOLT.
Given that, I think you need to display BDSKACCT, GRP and CUST from
InTrader, not from BOLT (they'll be Null when there's no entry in BOLT).
Remember, too, that you need to apply the Nz function to the value inside of
the Sum function: you need any Nulls converted to zeroes before they're
added, not after. As well, you cannot use ORDER BY in the subselects of a
UNION query.

SELECT InTrader.BDSKACCT, InTrader.GRP, InTrader.CUSP,
Sum(Nz(BOLT.PARVALUE,0)) AS
SumOfPARVALUE, Sum(Nz(BOLT.PRINCOST, 0)) AS SumOfPRINCOST,
Sum(Nz(BOLT.BOOKVALUE, 0)) AS
SumOfBOOKVALUE, Sum(Nz(InTrader.PARVALUE, 0)) AS SumOfPARVALUE1,
Sum(Nz(InTrader.PRINCOST, 0)) AS SumOfPRINCOST1, Sum(Nz(InTrader.BOOKVALUE,
0)) AS
SumOfBOOKVALUE1
FROM BOLT RIGHT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND
(BOLT.BDSKACCT = InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY InTrader.BDSKACCT, InTrader.GRP, InTrader.CUSP

If PARVALUE, PRINCOST and BOOKVALUE will always have a value when the row
exists, you could simplify that to

SELECT InTrader.BDSKACCT, InTrader.GRP, InTrader.CUSP,
Sum(Nz(BOLT.PARVALUE,0)) AS
SumOfPARVALUE, Sum(Nz(BOLT.PRINCOST, 0)) AS SumOfPRINCOST,
Sum(Nz(BOLT.BOOKVALUE, 0)) AS
SumOfBOOKVALUE, Sum(InTrader.PARVALUE, 0)) AS SumOfPARVALUE1,
Sum(InTrader.PRINCOST) AS SumOfPRINCOST1, Sum(InTrader.BOOKVALUE) AS
SumOfBOOKVALUE1
FROM BOLT RIGHT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND
(BOLT.BDSKACCT = InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY InTrader.BDSKACCT, InTrader.GRP, InTrader.CUSP

I think your complete query needs to be:

SELECT InTrader.BDSKACCT, InTrader.GRP, InTrader.CUSP, Sum(Nz(BOLT.PARVALUE,
0)) AS
SumOfPARVALUE, Sum(Nz(BOLT.PRINCOST, 0)) AS SumOfPRINCOST,
Sum(Nz(BOLT.BOOKVALUE, 0)) AS
SumOfBOOKVALUE, Sum(Nz(InTrader.PARVALUE, 0)) AS SumOfPARVALUE1,
Sum(Nz(InTrader.PRINCOST, 0)) AS SumOfPRINCOST1, Sum(Nz(InTrader.BOOKVALUE,
0)) AS
SumOfBOOKVALUE1
FROM BOLT RIGHT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND
(BOLT.BDSKACCT = InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY InTrader.BDSKACCT, InTrader.GRP, InTrader.CUSP
UNION
SELECT BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP, Sum(Nz(BOLT.PARVALUE, 0)) AS
SumOfPARVALUE, Sum(Nz(BOLT.PRINCOST, 0)) AS SumOfPRINCOST,
Sum(Nz(BOLT.BOOKVALUE, 0)) AS
SumOfBOOKVALUE, Sum(Nz(InTrader.PARVALUE, 0)) AS SumOfPARVALUE1,
Sum(Nz(InTrader.PRINCOST, 0)) AS SumOfPRINCOST1, Sum(Nz(InTrader.BOOKVALUE,
0)) AS
SumOfBOOKVALUE1
FROM BOLT LEFT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND (BOLT.BDSKACCT
= InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP
ORDER BY InTrader.BDSKACCT, InTrader.GRP, InTrader.CUSP;



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your input Eileen and Doug, but I could not get either
suggestion
to work. I was hoping that using the simple example before would make it
easier to see the format. Anyway, here is the full sql code that I am
working with:

SELECT BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP, Sum(BOLT.PARVALUE) AS
SumOfPARVALUE, Sum(BOLT.PRINCOST) AS SumOfPRINCOST, Sum(BOLT.BOOKVALUE) AS
SumOfBOOKVALUE, Sum(InTrader.PARVALUE) AS SumOfPARVALUE1,
Sum(InTrader.PRINCOST) AS SumOfPRINCOST1, Sum(InTrader.BOOKVALUE) AS
SumOfBOOKVALUE1
FROM BOLT RIGHT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND
(BOLT.BDSKACCT = InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP
ORDER BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP
UNION SELECT BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP, Sum(BOLT.PARVALUE) AS
SumOfPARVALUE, Sum(BOLT.PRINCOST) AS SumOfPRINCOST, Sum(BOLT.BOOKVALUE) AS
SumOfBOOKVALUE, Sum(InTrader.PARVALUE) AS SumOfPARVALUE1,
Sum(InTrader.PRINCOST) AS SumOfPRINCOST1, Sum(InTrader.BOOKVALUE) AS
SumOfBOOKVALUE1
FROM BOLT LEFT JOIN InTrader ON (BOLT.GRP = InTrader.GRP) AND
(BOLT.BDSKACCT
= InTrader.BDSKACCT) AND (BOLT.CUSP = InTrader.CUSP)
GROUP BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP
ORDER BY BOLT.BDSKACCT, BOLT.GRP, BOLT.CUSP;

I need to have zeros returned for any field that is null except for each
of
the two bdskacct, grp, and cust fields. These will require a IIf
statement
with a "Not found" put in the Null Fields. Hopefully, I can handle that
one.

Anyway, if either of your guys could help me with this big mess, I would
greatly appreciate it.

Thanks,
Phillip

For any numeric field if you use the nz statement that will work.
For example

SELECT nz([a.category],0), nz([a.amount],0) etc.........

Hope that helps.

Thanks
Eileen

Here is an easy one for you guys. How can I modify this query to
return
zeros for any null field that may be returned in this query. I know
that
it
will probably be an Iff statement or Nz, but I cannot seem to find an
example
of this type of right, left, union query, with replacing the resulting
null
fields with zeros. I know that I could write another query, but I
really
need it to be only one query. Any help would be greatly appreciated.

SELECT A.Category, A.Amount AS AmountA, B.Amount As AmountB
FROM Table1 AS A RIGHT JOIN Table2 AS B
ON A.Category = B.Category
UNION
B.Category, A.Amount AS AmountA, B.Amount As AmountB
FROM Table1 AS A LEFT JOIN Table2 AS B
ON A.Category = B.Category