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

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.

Answer

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:

Advertisement

Answer