In query ,search largest number from 3 different cloumns and show it in new column

[irslan rafique]

Hi,
In access 2010,I have one query based on a table.There are 3 different columns:
Commission 1, Commission 2 and Commission 3.
4th column is: Total Commission
So i want query automatically search the largest number in 3 above columns and add 10 and that has to be showed in 4th column.
Please assist me that is it possible in query or in its related form.

Thanks in advance.

[jforbes]

I think you are looking for something like:

SELECT Commissions.[Commission 1]
, Commissions.[Commission 2]
, Commissions.[Commission 3]
, Switch(
  [Commission 1]>=[Commission 2] And [Commission 1]>=[Commission 3],[Commission 1]
 ,[Commission 2]>=[Commission 1] And [Commission 2]>=[Commission 3],[Commission 2]
 ,[Commission 3]>=[Commission 1] And [Commission 3]>=[Commission 2],[Commission 3]
 )+10 AS [Total Commission]
FROM Commissions;

If you were using TSQL, you would use the SELECT CASE

You could also use a function if this is something you plan on doing more than once, that way you would have only one place to maintain the code.

[irslan rafique]

Thanks for the reply but how can i use select in 'MS Access query' and where i have to put that code. Sorry for asking again.Please be more specific.
Thanks

[jforbes]

While in Design View of your Query, from the Ribbon, select SQL View.
Or
Since what you are most in need of adding is the Switch Statement, you can just copy and paste:

Switch(
   [Commission 1]>=[Commission 2] And [Commission 1]>=[Commission 3],[Commission 1]
  ,[Commission 2]>=[Commission 1] And [Commission 2]>=[Commission 3],[Commission 2]
  ,[Commission 3]>=[Commission 1] And [Commission 3]>=[Commission 2],[Commission 3]
  )+10 AS [Total Commission]

into the Field portion of the Query in Design View and modify it to fit your purpose.

[irslan rafique]

HATS OFF! Thank you so much for assisting me..Thanks a lot