Query to Sum values from one table based on values from another table, considering null values-CodePudding

I have two tables in MS Access. One for contracts, with contract id and total value. Another one for the payments made in each contract.

So for example when a client gets a new contract for a total value of USD 100.00 then he can pay it in any amount he want up to the total value, like two payments of 50.00 or one of 30.00 and another of 70.00 or even one hundred payments of 1.00. And those payments go to the payments table with the contract id and the payment amount.

I need to make a query to find how much do they still have to pay for each contract. What is the net amount for each contract.

I managed to make a query that gives me the result I need, but if the contract is new and there are no payments on the payments table for this new contract, the net amount shown in my query is zero (blank field) and I needed it to show the total value of the contract instead.

For example Contract of 100.00 with two payments of 10.00 and 15.00 should show the net value as 75.00

Contract of 100.00 with no payments should show net value as 100.00

Here is what I have so far:

'Query 1

SELECT Contracts.[Contract ID],
 Sum(Payments.[Payment Value]
FROM Contracts 
LEFT JOIN Payments 
ON Contracts.[Contract ID] = Payments.[Contract ID]
GROUP BY Contracts.[Contract ID]

Query 2:

    SELECT Contracts.[Contract ID], 
Contracts.[Contract Value], 
Query1.[SumOfValue Payment Value], 
[Contract Value] - [SumOfValue Payment Value]
INTO NetValues
FROM Contracts
INNER JOIN Query1 
ON Contracts.[Contract ID] = Query1.[Contract ID]

Basically the Query 1 , sums the payments for each contract and Query 2 calculates the net value (total value - paid amount)

If there is any better way to achieve all of this please let me know.

CodePudding user response：

You can do it as a single query as follows:

SELECT   c.[Contract ID], 
         c.[contract value], 
         p.Paid, 
         c.[contract value]-nz(p.paid) AS Remaining
FROM     Contracts AS c 
  LEFT JOIN 
         (SELECT [Contract ID], sum([Payment Value]) as paid
          FROM Payments
          group by [Contract ID])  AS p 
    ON    c.[Contract ID] = P.[Contract ID];

Given these two tables:

The query will produce this result:

CodePudding user response：

" ... if the contract is new and there are no payments on the payments table for this new contract, the net amount shown in my query is zero (blank field)"

Actually, when you have no payments recorded for a given contract, Query1 gives you Null, not zero, as the sum of the payments. And any number minus Null gives you Null, not that first number.

If you want the Null treated as zero, use the Nz Function

Here's an Immediate window example of those issues:

Contract_Value = 100.00
SumOfValue_Payment_Value = Null

? Contract_Value - SumOfValue_Payment_Value
Null

' use Nz() to substitute zero for Null
? Contract_Value - Nz(SumOfValue_Payment_Value, 0)
 100

You can do the same in Query2 by including this:

[Contract Value] - Nz([SumOfValue Payment Value], 0)