Please see the attached, this has three queries:
OrderTotals
Code:
SELECT ORDER_TRANSACTION.OrderID_PK, Sum([ProdPrice]*[QuantityOrdered]) AS TotalDueFROM ORDER_TRANSACTION INNER JOIN ORDER_TRANSACTION_DETAIL ON ORDER_TRANSACTION.OrderID_PK = ORDER_TRANSACTION_DETAIL.OrderID_FK
GROUP BY ORDER_TRANSACTION.OrderID_PK;
PaymentTotals
Code:
SELECT ORDER_PAYMENT.OrderID_FK, Sum(ORDER_PAYMENT.PaymentAmount) AS TotalPaidFROM ORDER_PAYMENT_MODE INNER JOIN ORDER_PAYMENT ON ORDER_PAYMENT_MODE.ModeID_PK = ORDER_PAYMENT.ModeID_FK
GROUP BY ORDER_PAYMENT.OrderID_FK;
Finally those two joined to provide a balance
TransactionBalances
Code:
SELECT OrderTotals.OrderID_PK, OrderTotals.TotalDue, PaymentTotals.TotalPaid, [TotalDue]-[TotalPaid] AS BalanceFROM OrderTotals LEFT JOIN PaymentTotals ON OrderTotals.OrderID_PK = PaymentTotals.OrderID_FK;
Newsampledb_withQueries.zip