Search code examples
ms-access-2016

Select most recent from multiple columns and combine into a single field

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

Example.

USER FieldB FieldC
1 1/1/2020 2/1/2020
2 1/1/2020 -
3 - 3/1/2020
4 - -

And I need the data returned in like this.

USER FieldB
1 2/1/2020
2 1/1/2020
3 3/1/2020
4 -

Any help is greatly appreciated. Thank you in advance.

EDIT: Got it to work with the following query. I'm sure there is a more elegant solution.

SELECT
USER, Max(FieldB) as FieldD
FROM ( SELECT
       USER, FieldB
       FROM Table
       UNION
       SELECT
       USER, FieldC
       FROM Table) as T1
GROUP BY USER
Solution

  • Another way without using UNION

    SELECT Table.User, IIf(FieldB>FieldC,FieldB,FieldC) AS NewField
FROM [Table];