Taking the max of two values in query

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?
Who is Participating?
answer_dudeConnect With a Mentor Commented:
Here's an updated view...
If table 1 has one record for balance per account and table 1 has one record per account with a min due...   just join on account where Type=Balance equals and use a calculated field to compare the Balance Amount to the Min Due... and If the balance is less than the min. due for that account, then use the Min. Due Amount (although I think you meant that if the balance is greater than the minimum due then you want to use the minimum... ?)

See pics attached...

Use a "CASE" in your query. I don't know the exact syntax but google does. =) It goes something like this:

SELECT column1, column2 = CASE Column3 WHEN < Column4 THEN (SELECT column5 FROM tbl2 WHERE ...) ELSE (SELECT column6 FROM tbl3 WHERE ...) FROM tbl1

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

To find the highest of two values a and b just do...

iif(a>b, a, b)
BBluAuthor Commented:
Thanks for all of the screenshots.  They are very helpful.  I want the new recordset table to show all of the records in the original (left table).  I just want the "Balance" record to show the min.due amount if the amount in the records is less than that.
In that case... take the "Balance" criteria out of the query and change the Payment formula to:

Payment: IIf([Type]="Balance",IIf([Amount]<[MinDue],[MinDue],[Amount]),Null)

((side note:  if you really want the min due to come up when the balance is large then just change the operator))
BBluAuthor Commented:
Oh..okay.  Thanks, answer_dude.  I just realized that I have to check the min. due against the payments already made..not against the balance.  But that is extremely helpful.  Thank you.
You're welcome... good luck!
All Courses

From novice to tech pro — start learning today.