sql - Access - retrieving status and date puzzle

Question

In Access 2016, I have the following tables:

Table1
------
| ID | FK_ID | Status_date |Status_ID |
---------------------------------------
| 1  | 11    | d1          | 1        |
| 2  | 11    | d2          | 2        |
| 3  | 22    | d3          | 3        |
| 4  | 22    | d4          | 3        |

LookupTable1
------------
| OBJ_ID | Status |
-------------------
| 1      | A      |
| 2      | B      |
| 3      | C      |

And I would like to produce the following result. This will ultimately be exported to Excel.

xls report
==========
| FK_ID | Status_1_date | Status_2_date | Status_3_date |  <-- these will be aliased
=========================================================
| 11    | d1            | d2            |               |
| 22    |               |               | d4            |

The part of the puzzle I'm struggling with is that there seem to be at least these different ways to achieve this a) multiple Access queries b) a single Access query with in-line queries (possible?) c) VBA code d) in SQL Server itself e) other... What's the simplest way to create and maintain this, as the LookupTable1.Status values will change.

Answer 1

Looks like a simple CROSSTAB query:

TRANSFORM Max(Table1.Status_date) AS MaxOfStatus_date
SELECT Table1.FK_ID
FROM Table1
GROUP BY Table1.FK_ID
PIVOT Table1.Status_ID;

If you want the output to show the Status alias values, first JOIN the two tables then use the Status field as column header in CROSSTAB.