title: Select most recent from multiple columns and combine into a single field
body: <p>I have a table where I'm trying to get the lastest date from either field b or field c per user. and then combine that into a single field.  Using MS Access 2016</p>
  <p>Example.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">USER</th>
  <th style="text-align: center;">FieldB</th>
  <th style="text-align: center;">FieldC</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">1/1/2020</td>
  <td style="text-align: center;">2/1/2020</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">1/1/2020</td>
  <td style="text-align: center;">-</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">-</td>
  <td style="text-align: center;">3/1/2020</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">-</td>
  <td style="text-align: center;">-</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>And I need the data returned in like this.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">USER</th>
  <th style="text-align: center;">FieldB</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2/1/2020</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">1/1/2020</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">3/1/2020</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">-</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Any help is greatly appreciated.  Thank you in advance.</p>
  <p>EDIT:  Got it to work with the following query. I'm sure there is a more elegant solution.</p>
  <pre><code>SELECT
  USER, Max(FieldB) as FieldD
  FROM ( SELECT
         USER, FieldB
         FROM Table
         UNION
         SELECT
         USER, FieldC
         FROM Table) as T1
  GROUP BY USER
  </code></pre>