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:
Amount2:iif([Type]="Balance",max([Amount],[MinTotalToDate]-[Amount]),[Amount])

But, apparently I can't use the max function in that context.  

Can anyone suggest an alternative method?
BBluAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

answer_dudeCommented:
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...
query1.bmp
0
utter77Commented:
Hi.

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

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

iif(a>b, a, b)
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BBluAuthor Commented:
answer_dude-
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.
0
answer_dudeCommented:
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))
0
answer_dudeCommented:
Here's an updated view...
query2.bmp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
answer_dudeCommented:
You're welcome... good luck!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.