vba - Transpose a set of values in rows to columns in Access 2010

Question

I have an Access database that looks something like this:

ID     |   TestDate   |  Test1  |  Test2  |  Test 3  |
1      |    Date1     |   10    |    20   |    25    |
1      |    Date2     |   8     |    21   |    23    |
1      |    Date3     |   9     |    18   |    23    |
2      |    Date1     |   13    |    19   |    22    |

I wanted to transpose the row data to columns and maintain the name of the previous column headings, like so:

ID = 1
       |   Date1   |  Date2  |  Date3  |   etc...
Test1  |    10     |    8    |    3    |
Test2  |    20     |   21    |   18    |
Test3  |    25     |   23    |   23    |

ID = 2
       |   Date1   |
Test1  |    13     |
Test2  |    19     |
Test3  |    22     |


*The Date1 in the different IDs need not be the same. Date1 is the date the ID 
 had the Test for the first time. 

This way it will be easier to monitor the trend of the test values. I tried looking but the queries I stumble upon add values. I just need the data transposed without having to copy and paste into excel. Any MS Access query or VBA code is much appreciated. Thank you.

Answer 1

How about:

TRANSFORM SUM(q.testval) AS sumoftestval
SELECT q.id,
       q.test
FROM   (SELECT t2.id,
               t2.testdate,
               "test1"  AS Test,
               t2.test1 AS TestVal
        FROM   t2
        UNION ALL
        SELECT t2.id,
               t2.testdate,
               "test2"  AS Test,
               t2.test2 AS TestVal
        FROM   t2
        UNION ALL
        SELECT t2.id,
               t2.testdate,
               "test3"  AS Test,
               t2.test3 AS TestVal
        FROM   t2) AS q
GROUP  BY q.id,
          q.test
PIVOT q.testdate; 

Select an ID

TRANSFORM Sum(q.testval) AS sumoftestval
SELECT q.test
FROM (SELECT t2.id,
               t2.testdate,
               "test1"  AS Test,
               t2.test1 AS TestVal
        FROM   t2
        UNION ALL
        SELECT t2.id,
               t2.testdate,
               "test2"  AS Test,
               t2.test2 AS TestVal
        FROM   t2
        UNION ALL
        SELECT t2.id,
               t2.testdate,
               "test3"  AS Test,
               t2.test3 AS TestVal
        FROM   t2)  AS q
WHERE q.id=1
GROUP BY q.test
PIVOT q.testdate;