I'm working on this application that combines commissions data with payments by way of a union query. This query includes a record for the balance. What I'd like to do is introduce a new table to the union that checks for any minimum payments due. Specifically, I'd like to total all minimums due for each account, but for only the records with dates prior to today's (the date the summary statement is ran) date.
In the current union query, Account 12345 might show
01/03 $500 Commission
02/01 $1000 Commission
02/15 ($700) Pmt
04/07 $800 Balance
So if I have a table with minimums due to this account as follows:
I'd like the balance, instead of showing $800, to show$1500 (I would exclude summing the 4/15 min. because it's after the date the summary is ran (today's date)
Any ideas how I might approach that? I basically need to check if the balance is less than the total of the minimums to date, returning the larger of the two. But I don't know how to accomplish that.