Possible to grab Max Value across columns?

[sstincone]

Hi, I have a db where there are 4 columns. The first column is the account number and the 3 other columns have numeric values. I would like to know if its possible to see which column has the max value?

e.g

Account Abs 1 Abs 3 Abs 4
123456 17 25 40

Is their a way it can return 40 for me? I want to be able to find the max value.

Thank you in advance.

 

[pbaldy]

One way:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209857

 

[RuralGuy]

You might want to take the time now to re-examine the normalization of your table as well. Just a gentle nudge.

 

[sstincone]

I actually agree. I was able to do it in Excel with no problems but my mgr is insisting to have it done Access since it will be done every month. He claims there is a way by writing an if statement. I'll have him show me and if it works. I'll post it.

Thanks a

 

[pbaldy]

I certainly agree that it's not a good design. You'll be a lot happier in the long run if you normalize the data. That said, it can be done with IIf() functions embedded in one another, but it would be a lot messier than the above function.

 

[kapil0712]

IIf([A]>,IIf([A]>[C],[A]),IIf(>[C],IIf(>[A],),IIf([C]>,IIf([C]>[A],[C]))))

this will work
Thanks
kapil

 

[Khalil Islamzada]

Hi kapil0712:

If it will be 5 columns what would be the iif formula?

And need to know the minimum value for these 5 columns



Thanks in advance.