CODEWITHSUNDEEP
sqlms-accessmergems-access-2016ms-access-query
DooDoo
DooDoo

Reputation: 13479

Get the latest values in each column in MS Access

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.

Upvotes: -1

Views: 112

Answers (1)

id'7238
id'7238

Reputation: 2631

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 <> ".

Upvotes: 3

Related Questions