How to use VBA to transpose data

[Guest]

Hi! I have such data in Excel's worksheet "Sheet1"

date id test1 test2 test3
20070801 a1 (e-mail address removed) (e-mail address removed)
20070801 a2 (e-mail address removed)
20070802 a3 (e-mail address removed) (e-mail address removed) (e-mail address removed)
20070803 a4 (e-mail address removed) (e-mail address removed)

How can I use ACCESS VBA to import data to table "test" and transpose data
as below? Thanks.
date id test
20070801 a1 (e-mail address removed)
20070801 a1 (e-mail address removed)
20070801 a2 (e-mail address removed)
20070802 a3 (e-mail address removed)
20070802 a3 (e-mail address removed)
20070802 a3 (e-mail address removed)
20070803 a4 (e-mail address removed)
20070803 a4 (e-mail address removed)

 

[Guest]

Use a union query.
SELECT YourDate, ID, Test1 AS Test
FROM YourTable
UNION ALL SELECT YourDate, ID, Test2 AS Test
FROM YourTable
UNION ALL SELECT YourDate, ID, Test3 AS Test
FROM YourTable;

 

[Jeff Boyce]

I seem to recall that Excel offers a "Transpose" function...

Regards

Jeff Boyce
Microsoft Office/Access MVP