title: In Access, how do I display the id and column name of the column with the highest value in each row?
body: <p><strong>This is the sample table:</strong></p>
  
  <pre><code>Table1
  | id | one | two | three | four | five | six |
  |--------------------------------------------|
  |   1|   11|    7|      4|     9|     4|    1|
  |   2|   12|    9|      3|     8|    19|   32|
  |   3|   18|    7|      7|     1|    24|    2|
  |   4|    9|    1|     15|     6|     6|    4|
  </code></pre>
  
  <p><strong>Currently the query I am using on the table is this:</strong></p>
  
  <pre><code>SELECT id, Max(colx) AS colWithMax
  FROM (
     SELECT id, one AS Colx From Table1 UNION ALL
     SELECT id, two AS Colx From Table1 UNION ALL
     SELECT id, three AS Colx From Table1 UNION ALL
     SELECT id, four AS Colx From Table1 UNION ALL
     SELECT id, five AS Colx From Table1 UNION ALL
     SELECT id, six AS Colx From Table1
  )
  group by id;
  </code></pre>
  
  <p><strong>The output I'm getting is:</strong></p>
  
  <pre><code>ID | colWithMax
  -- | ---------
   1 |     11
   2 |     32
   3 |     24
   4 |     15
  </code></pre>
  
  <h1>My question is how can I change my query so my output will show the column name instead of the value in the column?</h1>
  
  <p><strong>Based on the sample table the desired output would be:</strong></p>
  
  <pre><code>ID | colWithMax
  -- | --------- 
   1 |   one
   2 |   six
   3 |   five
   4 |   three
  </code></pre>
  
  <p><em>Note: I have basic understanding of sql and I have never used ms-access before.  Please be as descriptive as possible in your answers.  Thank you.</em></p>