Payment Vs Total Order Value

[isons via AccessMonster.com]

I have two tables in my database. These are

1- Orders
2- Payments

Structure for Table ORDERS IS

POID (AUTONUMBER)
DATE
PARTNO
DESCRI
RATE
TOTAL
SupplierID

Structure of Payments Table

PaymentID (Autonumber)
SupplierID
Date
PaymentAmount

I have designed 2 querries which shows Total payments paid to suppliers and
Total Order Value.

I want to link both tables so that I can know how much payments have been
made and what is the remaining balance to be paid.

I have tried several things but somehow I could'nt do the right way.

Please help

 

[John Spencer]

SELECT O.SupplierID
, Sum(O.Total) as OrderTotals
, NZ(Sum(P.PaymentAmount),0) as TotalPayments
, Sum(O.Total) - Nz(Sum(P.PaymentAmount),0) as Balance
FROM Orders As O Left Join Payments as P
ON O.SupplierID = P.SupplierID
GROUP BY O.SupplierID