Find Maximum Across Multiple Fileds?

[quirkyjoe]

I have four fields containing numbers, e.g., Field1, Field2, Field3,
Field4.

For each record, how do I find the maximum value from the four fields?

Also, since I am asking, once I find the maximum is it easy to have it
spit out from which field the maimum came from?

Thanks!

 

[John Nurick]

Hi Joe,

The fact that you need to do this suggests that your database structure
may be flawed, with "repeating fields" like you'd use in a spreadsheet
for the 12 months of a year.

The correct way to handle this sort of thing in a relational database is
to "normalise" the data into two or more related tables. E.g. instead of

ID, PartName, SalesJan, SalesFeb, SalesMar, SalesApr...
83, Floggle Toggle, 147, 439, 387, 623...
97, Widget Alpha, 20, 4543, 200, 185...

You'd have two tables

tblParts
ID, PartName
83, Floggle Toggle
97, Widget Alpha

tblSales
ID, MonthBeginning, Quantity
83, 1 Jan 2006, 147
97, 1 Jan 2006, 20
83, 1 Feb 2006, 439
97, 1 Feb 2006, 4543
...

With this structure, it's easy to identify maximums, minimums, averages
and so on.