Please consider this table in MS Access:
Id Year Month F1 F2 F3 F4
----------------------------------------------------------------------
1 2020 1 A B 1 2
1 2020 2 AA
1 2020 3 BB 11
2 2020 1 3 4
2 2020 2 F G
I want to have this result:
Id F1 F2 F3 F4
-----------------------------------------------
1 AA BB 11 2
2 F G 3 4
I want the latest value in each column and merge them in one row for each Id.
How can I achieve this result using a query in MS Access.
SELECT
Id,
(SELECT TOP 1 F1 FROM test t1 WHERE t1.Id = t.Id AND F1 IS NOT NULL ORDER BY [Year] DESC, [Month] DESC) AS F1,
(SELECT TOP 1 F2 FROM test t1 WHERE t1.Id = t.Id AND F2 IS NOT NULL ORDER BY [Year] DESC, [Month] DESC) AS F2,
(SELECT TOP 1 F3 FROM test t1 WHERE t1.Id = t.Id AND F3 IS NOT NULL ORDER BY [Year] DESC, [Month] DESC) AS F3,
(SELECT TOP 1 F4 FROM test t1 WHERE t1.Id = t.Id AND F4 IS NOT NULL ORDER BY [Year] DESC, [Month] DESC) AS F4
FROM (
SELECT DISTINCT Id
FROM test
) t
If the empty cells in your example are empty strings instead of NULL, then the condition in the subquery will be F1 <> ".