# Thread: Calculating with Null values

1. Registered User
Join Date
Aug 2003
Location
New Zealand
Posts
1

## Unanswered: Calculating with Null values

Gone right back to first principles on this one.
I have set up two tables "Bank" with 2 fields Account and Balance. Account is primary key which is relationshipped to table 2 "Sales" which also has 2 fields Account and Sales.
Have put some figures in including some "\$0.00" for Bank and in Sales have only entered a few.

I am trying to make a query list ALL bank accounts and add any Sales that refer to the same account.
I know it involves Null values and have tried every permutation I can think of but the closest I have got was a result where each account concatinated each entry in the sales figures ignoring the account.

I tried Nz'ing but must be in the wrong place.

Where are things supposed to go and exactly how are they supposed to be added to show All including zero results.

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```select b.account
, b.balance + iif(isnull(s.balance), 0, s.balance)
from bank b
left outer
join sales s
on b.account = s.account```
rudy
http://r937.com

3. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447
Rudy: The bank balance may be NULL, too, and you are getting a record per Sales, and not per Bank. This query should do the job:

SELECT Bank.Account,
Min(IIf(IsNull([bank].[Balance]),0,[bank].[Balance])) AS [Bank Balance],
Sum(IIf(IsNull([Sales].[Balance]),0,[Sales].[Balance])) AS [Sales Balance]
FROM Bank LEFT JOIN Sales ON Bank.Account = Sales.Account
GROUP BY Bank.Account;

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
good point about summing the sales to get one record, that was a real mistake on my part (i was tired last night)

but the bank balance would not be null -- there's no way

how could you have an account with an unknown balance?

shoot the application programmer!!!

the IIF(ISNUL()) is only to guard against the unmatched rows in the left outer join

rudy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•