Amount NOT Found...

[Gary B]

Table: Payments

PaymentsKey, Amount
967, $50.00


Table: Applied

PaymentsKey, AppliedAmount
967, $22.00
967, $13.00
967, $05.00


This will return TotalAmountApplied:

SELECT PaymentsKey, Sum(Applied.AppliedAmount) AS SumOfAppliedAmount
FROM Payments INNER JOIN Applied ON Payments.PaymentsKey =
Applied.PaymentsKey
WHERE (((Payments.PaymentsKey)=667))
GROUP BY Payments.PaymentsKey

But, How do I return the Amount UNAPPLIED or from the above example, $10.00

Thank you

 

[PC Datasheet]

Create a query based on the Applied table. Include both fields. Click on the
Sigma (looks like E) button on the toolbar at the top of the screen. Under
AppliedAmount, change Group By to Sum. Create another new query based on the
preceding query and the Payments table. Pull down PaymentsKey from the
Payments table. In the second field of the query enter this calculated
field:
UnAppliedAmount:[Amount] - [SumOfAppliedAmount]

This query will give you the unapplied amount for each PaymentsKey.