Hi All,

I need help on the syntex for example below.

Contract # Tier Price
12345 1 3
12345 2 2
12345 3 5
22345 1 6
22345 2 4
33333 -1

I want to have a formula that gives me the price for the highest tier for each of the contract and if the contract has -1 in tier then give me the price at 0. Therefore, from the sample above, my result of this formula will provide me contract #12345 @ price of 5; contract #22345 @ price of 4 and contract #33333 @ price 0.

Anyone? Thank you

Hi grchang,


I think you might get the results from a query.

Create a new query and select the table where your data is held.

Select fields Contract then in manually create the following calculated field:

PriceA: Iif([Tier] = -1, 0, [Price])

If the Tier is -1 then the Iif returns 0 otherwise it returns the actual price. An Iif statement may slow down a query's performance if there are a large number of records.

In the query builder, click on the totals button. Set Contract Total to 'Group By' and PriceA Total as 'Max'.

This should give you the max price for each contract number.