Highest number in each record

[terre]

Hi folks

This should not be so hard....

I have a table with columns A,B,C,D
A B C D
21 23 15 20
24 21 23 29
23 21 26 21

I want column E to show the name of the column with the largest number
as shown.
A B C D
21 23 15 20 B
24 21 23 29 D
23 21 26 21 C

Is there a query, or way to do this?

Thanks

Terre

 

[MajP]

To do this in pure sql (no using a vba function) you have to normalize the date. If you do not have a PK for each row add one. You can add an autonumber. Then you can do a union query to normalize your data.

Select 
 ID,
 "A" as ColName,
  A as ColValue
From
 Your Table
Union
Select
 ID,
 "B" as ColName,
  B as ColValue
From
 Your Table
....

Now your data would look like this

ID  ColName ColValue
1   21       A
1   23       B
1   15       C
1   20       D
2   24       A
2   21       B
2   23       C
2   29       D
...
3   21       D

Now you have to do a Max by Group query. There are several ways to do that. Here is a good explanation

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

 

[terre]

Of course I meant Row, not column

Sorry.