Q:

Access 2013: Pivot multiple columns

I have a strange access database table that I need to pivot before using with Tableau. I'm using Access 2013. Data is totally fake but the structure is right.
There are 200 files, with about 1500 runs each. I have 50 years of metrics. I have about 10 metrics per file/run. 
+--------------------------------------------------------+
| DataTable                                              |
+-------------+--------------+------+------+------+------+
| FileIDRunID |    Metric    | 1999 | 2000 | 2001 | 2002 |
+-------------+--------------+------+------+------+------+
| 00000100001 | Breakfast    | 45   | 47   | 48   | 49   |
| 00000100001 | Lunch        | 27   | 37   | 50   | 99   |
| 00000100002 | Breakfast    | 45   | 47   | 48   | 49   |
| 00000100002 | Lunch        | 27   | 37   | 50   | 99   |
| 00000200001 | Breakfast    | 45   | 47   | 48   | 49   |
| 00000200001 | Lunch        | 27   | 37   | 50   | 99   |
| 00000200002 | Breakfast    | 45   | 47   | 48   | 49   |
| 00000200002 | Lunch        | 27   | 37   | 50   | 99   |
+-------------+--------------+------+------+------+------+

I would like to pivot and have the metrics as columns and the years as individual rows.
+------------------------------------------------+
| DataTableView                                  |
+-------------+--------------+-----------+-------+
| FileIDRunID |    Year      | Breakfast | Lunch |
+-------------+--------------+-----------+-------+
| 00000100001 | 1999         | 45        | 47    |
| 00000100001 | 2000         | 27        | 37    | 
| 00000100002 | 1999         | 45        | 47    | 
| 00000100002 | 2000         | 27        | 37    |
| 00000200001 | 1999         | 45        | 47    | 
| 00000200001 | 2000         | 27        | 37    | 
| 00000200002 | 1999         | 45        | 47    | 
| 00000200002 | 2000         | 27        | 37    | 
+-------------+--------------+-----------+-------+

I managed to pivot one year. 
TRANSFORM FIRST(DataTable.[1999])
SELECT FileIDRunID, '1999' as Year
FROM DataTable
GROUP BY FileIDRunID
PIVOT DataTable.Metric

I think the next step is to UNION ALL with each year manually. When I try that with the next year though I get an error.
TRANSFORM FIRST(DataTable.[1999])
SELECT FileIDRunID, '1999' as Year
FROM DataTable
GROUP BY FileIDRunID
PIVOT DataTable.Metric
UNION ALL
TRANSFORM FIRST(DataTable.[2000])
SELECT FileIDRunID, '2000' as Year
FROM DataTable
GROUP BY FileIDRunID
PIVOT DataTable.Metric

Syntax error (missing operator) in query expression 'DataTable.Metric
    UNION ALL
    TRANSFORM FIRST(DataTable.[2000])
    SELECT FileID, RunID, '2000' as Year
    FROM DataTable
    GROUP BY FileIDRunID
    PIVOT DataTable.Metric'
I'm hoping someone here has a better idea :). 

A:

TRANSFORM can only occur at the start of an SQL statement.
First unpivot all the years and only then pivot the metric. Now, as MS Access apparently
has its limits when it comes to complex queries (many unions), it is best to pass via 
an intermediate table:
SELECT  FileIDRunID, Metric, Year, Value
FROM    (
            SELECT      FileIDRunID, Metric, 1999 As Year, [1999] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2000 As Year, [2000] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2001 As Year, [2001] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2002 As Year, [2002] As Value
            FROM        DataTable
        )
INTO myTempTable;

I have union-ed 4 years, but you should check how far you can go before MS Access refuses the statement for being too complex. 
Then, add some more years, with the same number of unions:
INSERT INTO myTempTable (FileIDRunID, Metric, Year, Value)
SELECT  FileIDRunID, Metric, Year, Value
FROM    (
            SELECT      FileIDRunID, Metric, 2003 As Year, [2003] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2004 As Year, [2004] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2005 As Year, [2005] As Value
            FROM        DataTable
            UNION ALL
            SELECT      FileIDRunID, Metric, 2006 As Year, [2006] As Value
            FROM        DataTable
        );

etc... to finally do this:
TRANSFORM FIRST(Value)
SELECT   FileIDRunID,
         Year
FROM     myTempTable
GROUP BY FileIDRunID,
         Year
PIVOT    Metric

And then drop the temporary table and compress the database to get the original size back. If possible, redesign the database to use the completely unpivoted structure as in the temporary table.

