I have a join query with the following tables:
Table 1: all activity on an account (commissions earned, payments) and a balance (obtained in an earlier step); Each records is identified as either a commission earned, a payment, or the balance. There is only one Balance record per account.
Table 2: Min. due
This table shows the mins. due for each account
I'm attempting to create a new recordset with Account ID, Amounts for each transaction, and a balance. But, If the balance is less than the min. due for that account, I'd like to use the Min. Due Amount; otherwise I use the figure in the original Balance records.
I'm trying to use this in the criteria of the new Amount Field:
But, apparently I can't use the max function in that context.
Can anyone suggest an alternative method?