Basic Nz function issue

[UNC_Access]

Hi. I am nearly positive I'm not asking this question in the most efficient method possible, so any advice is welcome! I'm new to Access and am not a programmer (as is obvious).

Basically:

1. I have two tables: Table1 and Table2
2. Table1 has two fields: ID Number and Amount
3. Table2 has two fields: ID Number and Repayment
4. I need to create a query (linking ID Number) which calculates: Sum of Table1.Amount - Sum of Table2.Repayment for each ID Number to provide a ResultingBalance (please excuse the horrible wording). To reiterate:
-4a. In respect to Table1, I want to sum all amounts for each ID Number
-4b. In respect to Table2, I want to sum all repayments for each ID Number
-4c. Ultimately, my goal is to take the sum of all amounts described in #4a (above) and subtract the sum of all amounts described in #4b (above) to calculate a remaining balance

My issue, of course, is explained with a quick example:

Table1 has a record:

ID Number: 1111
Amount: $100

But Table2 contains no ID Number 1111 (and thus no Repayment amount)

Therefore, when I perform the query in #4 (above), for ID Number 1111:

Amount is $100
Repayment is blank
ResultingBalance is blank

I am trying to use the NZ function to substitute nulls with a zero in the query.

Is this possible?

Here is my SQL code if it helps (don't know where to put the NZ function):

SELECT Table1.[ID Number], Sum(Table1.Amount) AS SumOfAmount, Sum(Table2.Repayment) AS SumOfRepayment, Sum([Table1].[Amount]-[Table2].[Repayment]) AS Balance
FROM OP INNER JOIN Table2 ON Table1.[ID Number] = Table2.[ID Number]
GROUP BY Table1.[ID Number];

 

[plog]

I would do this with 2 sub-queries, one to total amounts and one to total repayments:

SELECT [ID Number], SUM(Amount) AS TotalAmount
FROM Table1
GROUP BY [ID Number];

Name that 'subAmounts'. Then use this SQL for a query names 'subRepayments':

SELECT [ID Number], SUM(Repayment) AS TotalRepayment
FROM Table2
GROUP BY [ID Number];

Now for the final query, use this SQL:

SELECT subAmounts.[ID Number], subAmounts.[TotalAmount] - Nz(subRepayments.[TotalRepayment], 0) AS Balance
FROM subAmounts LEFT JOIN subRepayments ON subAmounts.[ID Number] = subRepayments.[ID Number];

The key is the LEFT JOIN in the final query. It forces all the ID Numbers in Table1 to appear, even if there are no repayments.

 

[UNC_Access]

Thanks! I tried it and it worked!

One more twist:

How can I display in the final query all of the following four fields?:

ID Number
TotalAmount
TotalRepayment
Balance

When I try to add the TotalAmount and TotalRepayment fields, the same issue occurs.

SELECT subAmounts.[ID Number], subAmounts.TotalAmount, subRepayments.TotalRepayment, subAmounts.[TotalAmount]-Nz(subRepayments.[TotalRepayment],0) AS Balance
FROM subAmounts LEFT JOIN subRepayments ON subAmounts.[ID Number] = subRepayments.[ID Number]
GROUP BY subAmounts.TotalAmount, subRepayments.TotalRepayment;

 

[plog]

Where'd that GROUP BY clause come from? Remove that and put the TotalRepayment field in a NZ function.

 

[UNC_Access]

I see! Got it!

I actually learned a ton just from these basic instructions you provided.

New to programming, so thanks again!