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.
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:
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;
Make everything as simple as possible, but not simpler! - A. Einstein
DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool