URGENT!!! TRANPOSE OR ???

[jane30]

Hi, in my Access 2000 database, I have a table summarized by name as followings:

PersonName PGM1 PGM2 PGM3 PGM4 PGM5
John Smith 10(days) 5 3
Alice Young 2 1
John Test 7 8 10 6
Alice Test 3
Test Test 1 7 4 5 9

I need to list those PGM vertically by PersonName like this:
John Smith PGM1 10(days)
John Smith PGM2
John Smith PGM3 5
John Smith PGM4 3
John Smith PGM5
Alice Yound PGM1
Alice Yound PGM2 2
Alice Yound PGM3
Alice Yound PGM4 1
Alice Yound PGM5
etc. That's, list all the PGMs (PGM1 THRU PGM5) under each person's name and put corresponding days. Any suggestions?

Thanks a bunch.

 

[TonyJollans]

Hi jane30,

This SQL should do it .. Change Table9 to your Table name ..

SELECT Table9.PersonName, "PGM1" as Lit, Table9.PGM1 as PGM
FROM Table9
Union all
SELECT Table9.PersonName, "PGM2" as Lit, Table9.PGM2 as PGM
FROM Table9
Union all
SELECT Table9.PersonName, "PGM3" as Lit, Table9.PGM3 as PGM
FROM Table9
Union all
SELECT Table9.PersonName, "PGM4" as Lit, Table9.PGM4 as PGM
FROM Table9
Union all
SELECT Table9.PersonName, "PGM5" as Lit, Table9.PGM5 as PGM
FROM Table9
Order by Table9.PersonName, Lit;

Enjoy,
Tony