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.

 

[Pat Hartman]

Welcome aboard. Let me add my voice to RuralGuy's. Relational databases such as Access do not support functions that address columns in a table because if you have a set of columns in your table, you are violating first normal form which tells us we should not have any repeating groups. The repeating columns actually belong in a separate table where they will exist as three separate rows. Relational databases have plenty of functions that calculate against rows.

You will be much happier with Access as a solution if you normalize your data. If you want to leave it "flat", you will be happier with Excel.

 

[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.

 

[Pat Hartman]

If you simply normalize the table, you can use a query with a Min() function. If you don't normalize, you are worse off than you were with Excel since you have all the same problems that arise when you have to add a column and then rewrite all your formulas and none of the benefits of functions that work across columns (cells in spreadsheet speak).