How to use VBA to transpose data

  • Thread starter Thread starter Guest
  • Start date Start date
G

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)
 
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;
 
I seem to recall that Excel offers a "Transpose" function...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top