How to use VBA to transpose data

A

alexsas

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

date id test1 test2 test3
20070801 a1 [email protected] [email protected]
20070801 a2 [email protected]
20070802 a3 [email protected] [email protected] [email protected]
20070803 a4 [email protected] [email protected]

How can I use ACCESS VBA to import data to table "test" and transpose data
as below? Thanks.
date id test
20070801 a1 [email protected]
20070801 a1 [email protected]
20070801 a2 [email protected]
20070802 a3 [email protected]
20070802 a3 [email protected]
20070802 a3 [email protected]
20070803 a4 [email protected]
20070803 a4 [email protected]
 
K

KARL DEWEY

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;
 
J

Jeff Boyce

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

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top