Max function

[Tischtennisbal]

Dear all,

I have to calculate the highest rebate percentage during 3 meeting round. The rebates differ almost every round (can be higher, can be lower)

I want to know how I can calculate the highest rebate without showing the other lower values.

E.g.
Round 1 Round 2 Round 3
Product 1: 0,05 0,03 0,07
Product 2: 0,02 0,03 0,01

In this case the outcome (by use of query) should be:

Product 1: 0,07
Product 2: 0,03

[VALIS]

Dear all,

I have to calculate the highest rebate percentage during 3 meeting round. The rebates differ almost every round (can be higher, can be lower)

I want to know how I can calculate the highest rebate without showing the other lower values.

E.g.
Round 1 Round 2 Round 3
Product 1: 0,05 0,03 0,07
Product 2: 0,02 0,03 0,01

In this case the outcome (by use of query) should be:

Product 1: 0,07
Product 2: 0,03

A couple of questions
If two rounds have the same result for the rebate do you need to record this in the result?
Are there going to be move rounds?

If there are only 3 you could use a nested IIF statement to get the result in a Query.

MaxRebate:IIF(AND([Round 1]>[Round 2],[Round 1]>[Round 3]),[Round 1],IIF(AND(([Round 2]>[Round 1],[Round 2]>[Round 3]),[Round 2],[Round 3])

Any more rounds and it would get messy this way

[Tischtennisbal]

It doesn't matter if mutiple rebates are the same. As long the highest rebate is taken. And 3 is the maximum rounds.

I used your formula, but it gives an error:

"The expression your entered has an invalid (.) dot or ! operator or invalid parentheses."

I have the english version, but even changing comma's for dot comma (;) does not work.

Also should I put the formula is a new field (in the query) or can I put this as a criteria of one of the rebate levels.

This is the sql of my query

SELECT DISTINCTROW Mothercompany.Mothercompanyname, Rebates.[Rebate level 1], Rebates.[Rebate level 2], Rebates.[Rebate level 3]
FROM Mothercompany INNER JOIN Rebates ON Mothercompany.Mothercompanycode = Rebates.Mothercompanycode;

Maybe this will help you resolve my problem...

Thanks for your help!

[NeoPa]

Try :-

MaxRebate:IIf([Round 1]>IIf([Round 2]>[Round 3],[Round 2],[Round 3]),[Round 1],IIf([Round 2]>[Round 3],[Round 2],[Round 3]))

instead.
The AND() function isn't available within SQL like that.

[Tischtennisbal]

I did almost the same as you.

IIf([Rebate level 1]>=[Rebate level 2];[Rebate level 1];IIf([Rebate level 2]>=[Rebate level 3];[Rebate level 2];[Rebate level 3]))

But for some weird reason, it does not give all of the highest rebates...
It only gives around 40% of all records... Now I have no clue why

[NeoPa]

Try doing the same as me - see what happens then?

[Tischtennisbal]

Unfortunately still the same. I don't get all records.
Although I find it peculiar that the query returns more records if I put the formula @ the criteria for round 2 or 3 compared to round 1.

Also adding "MaxRebate:" to the formula gives me an error, but the formula works without... Any idea why?

[NeoPa]

Yes.
When I posted the code, I had no idea where you intended to use it (as it happens - in the Criteria property) so I formulated it as a field displayed in a query.
MaxRebate: just names the output of the column for clarity.

If you post exactly what you used in your criteria field (AND which field it is used in) - I'll check it over for you.

[Tischtennisbal]

This is my query:

SELECT DISTINCTROW Rebates.ContractSupplier, Rebates.Product, Rebates.[Rebate level 1], Rebates.[Rebate level 2], Rebates.[Rebate level 3]

CODE]IIf([Rebate level 1]>=[Rebate level 2];[Rebate level 1];IIf([Rebate level 2]>=[Rebate level 3];[Rebate level 2];[Rebate level 3]))[/code]

- When I put the formula in rebate level 1, 13 or 56 records are found;
- When I put the formula in rebate level 2, 14 of 56 records are found;
- When I put the formula in rebate level 3, 41 of 56 records are found.

Some rebate levels are the same. But that does not matter for the result. As long as the maximum is showed.

Thanks for your help! :-)

[NeoPa]

I'm getting more and more confused.
You keep posting code in here which is impossible (the ';' characters won't work in any of my tests).
Also, you talk about setting this code in your criteria field(s), yet when you post the SQL there is no WHERE clause for me to look through (which would be created if you had any criteria there at all).
I need you to copy and paste (not type out again as that introduces typos) the SQL that needs looking at.
I would like to help, but it is impossible unless you give the info requested in the form requested.

[Tischtennisbal]

SELECT DISTINCTROW Rebates.ContractSupplier, Rebates.[Rebate level 1], Rebates.[Rebate level 2], Rebates.[Rebate level 3]
FROM Rebates
WHERE (((Rebates.[Rebate level 3])=IIf([Rebate level 1]>=[Rebate level 2],[Rebate level 1],IIf([Rebate level 2]>=[Rebate level 3],[Rebate level 2],[Rebate level 3]))));


Comma's don't work with my english/american version of Access. Therefore you should substitute the dot-comma's ( ; ) with comma's. I reckon it will work then.

[NeoPa]

SELECT DISTINCTROW Rebates.ContractSupplier, IIf([Rebate level 1]>=[Rebate level 2],[Rebate level 1],IIf([Rebate level 2]>=[Rebate level 3],[Rebate level 2],[Rebate level 3])) AS MaxRebate
FROM Rebates;

I don't understand what you're saying about the 'semi-colon's ( ; ) - but that shouldn't be important.
If this code doesn't work then feel free to swap the commas out :s