Sum is producing null value 1

[ksolomon]

I have a query that is designed as follows (summarized):

Table 1
Emp #

Table 2
Emp #
Amout

My query has groupby emp # and sum amount > 101

If there is no amount > 101, the query returns null. I can
not display this value on a form or do anything with it.

Thanks for your help.

 

[r937]

can we see your query?

it should be something like

[tt]select T1.empno
, sum(T2.amount) as sumamount
from Table1 as T1
left outer
join Table2 as T2
on T1.empno = T2.empno
and T2.amount > 101
group
by T1.empno[/tt]

rudy

http://r937.com/

 

[TonyJollans]

Hi ksolomon,

So what, exactly, is your question? Can you be more specific and, perhaps, post your SQL.

Enjoy,
Tony

 

[MichaelRed]

look into the Nz function. Under the ubiquitous {F1} (a.k.a. HELP).




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ksolomon]

SELECT Table1.[Unique Number], Sum(Table2.[Amount Donated]) AS [SumOfAmount Donated]
FROM Table1 LEFT JOIN Table2 ON Table1.[Unique Number] = Table2.[Your Number]
GROUP BY Table1.[Unique Number]
HAVING (((Sum(Table2.[Amount Donated]))>101));

The code is above. Instead of getting a zero when there is nothing >101 or when there are no records. I am getting a null. I can't get this value to display on forms. Thanks.

 

[TonyJollans]

Hi ksolomon,

You are only getting results when the total is greater than 101. To get results for all totals, just remove the HAVING clause. To make totals less than 101 show as zero, use an immediate if, and remove the HAVING ..

SELECT Table1.[Unique Number], Iif(Sum(Table2.[Amount Donated])>101,Sum(Table2.[Amount Donated]),0) AS [SumOfAmount Donated]
FROM Table1 LEFT JOIN Table2 ON Table1.[Unique Number] = Table2.[Your Number]
GROUP BY Table1.[Unique Number];

You seem to imply, however, that none of the above are what you actually want; if that is the case, please come back.

Enjoy,
Tony

 

[ksolomon]

Tony,
I only want results for totals > 101. The problem is that when there are no totals > 101, I get a null value. I am doing calculations on the value that is in amount donated and when I try to use that filed (amount donated) on a form, I get absolutely nothing.
Thanks for your help.

 

[MichaelRed]

A.[tab]Still not willing to visit the ubiquitous {F1}?

B.[tab]What is 'wrong' with Tony's soloution? Or what (unwanted) results do your get?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ksolomon]

I tried Tony's code, and it still returns a null for the sum.

 

[r937]

tony's solution doesn't check for null SUM() -- because it's a left join, there could be Table1 rows with no matching Table2 rows, in which case SUM() will be null, and the IIF will not catch that

so it should be

select iif(isnull(sum(Table2.[Amount Donated])),0
, iif(sum(Table2.[Amount Donated])>101
,Sum(Table2.[Amount Donated]),0)) as ...

rudy

 

[TonyJollans]

Hi ksolomon,

Now I'm lost. My query should return one record for each 'Unique Number', with a zero if the total is less than 101, otherwise the total; no nulls anywhere. Can you give a bit more detail? Are you interested only in donations > 101, or in totals > 101 regardless of individual donation size? and what do want to do with the results that you cannot do at the moment.

I do see that your original query will return no records if there are no totals > 101. In the same situation, my query will return records with zero totals. Same questions still apply

Enjoy,
Tony

 

[r937]

Tony, in a left outer join, unmatched rows return nulls in their columns

if SUM(something) is null, then any IIF on it will return null as well

thus the ISNULL()


rudy

 

[TonyJollans]

Hi Rudy,

I agree with you that checking for nulls wherever they may occur is good practice but conditions with null on one (or both) side(s) always return FALSE.

I tested my SQL before I posted and have just cut and pasted it and done so again and it returns 0 for all unmatched rows in Table1.

Enjoy,
Tony

 

[r937]

> conditions with null on one (or both) side(s)
> always return FALSE.

thanks, tony

that's news to me, but i can accept it (not having tested it myself)

just another example where access does not follow standard sql

in standard sql, any comparison involving null yields null


rudy

 

[ksolomon]

Tony,
I pasted your SQL statement and it does indeed return zero instead of null. Thank you for your assistance.

 

[TonyJollans]

Hi rudy,

It does get confusing, doesn't it?

My understanding is that the Iif function is part of Access, not SQL, so follows Access rules.

Enjoy,
Tony