SQL-Query: left join?

[key]

Hi Access-Friends,

I'm stuck with the following problem: in Tab1 are Account No. and Amounts and it is possible to change the amounts. The changed Number is stored in Tab2.
Now, I want to show the actual amounts, it means: if there is a changedAmount in Tab2 show the calculated amount. Example:
Tab1: Amount: 5000, Month: January, Year: 2002
Tab2: ChangedNumber: 2000 , Month: January, Year: 2002

result should be: 3000

if there is no ChangedNumber in Tab2 show 5000.

I've tried to use isNull, iif functions as well es left join - nothing works!

Could someone help me?

Many thanx,

Key


P. S. Here are the tables:

Tab1(AccNo, Amount, Month, Year) and
Tab2 (AccNo, ChangedAmount, Month, Year)

 

[Pat Hartman]

Use a Left join to join the tables on AcctNo, Month, and Year (Month and Year are the names of functions and should NOT be sued as column names). You will have to use the Nz() function to get the subtraction to work since one of the values may be null.

Amount - Nz(ChangedAmount, 0)

 

[key]

quick question..

First of all: thanx a lot, it works!!
To make myself happy: how do I implement a Sum function - because, an amount can be changed 0, one or more times, so what I need to do is something like:

(Amount)- Sum(Nz(ChangedAmount,0)...

But this doesn't work.

Many, many thanx,

Key

 

[Pat Hartman]

It should work. Did you include all the other query columns in the group by clause?