Transpose Columns to Rows Query

[mrszw]

Hi, i am encountering some problems and need help/suggestions on how to do this transpose. Basically, i wish to transpose the following table but is lost on how to achieve it in excel or using access queries. Would appreciate any help.

Original

Desired output

 

[pbaldy]

In Access you'd need a UNION query:

SELECT ID, Group, "Christian" AS Person, Christian
FROM TableName
WHERE Christian Is Not Null
UNION ALL
SELECT ID, Group, "Johnny" AS Person, Johnny
FROM TableName
WHERE Johnny Is Not Null
...

 

[mrszw]

thanks pabaldy! works great!

 

[pbaldy]

No problem; welcome to the site by the way!

 

[sgundlac]

Hi.
I have a similar question for transposing columns to rows using the Union All qry. I created the union qry to include all the columns (Christian, Johnny, Steve). However sometimes my data only has some but not all of the columns (example only Christian and Steve), and when I run the union qry I get a message box "Enter Parameter Value"" looking for the column Johnny. Is there a way to create VBA code to map the original data with only Christian and Steve into a normalized table with all 3 fields- Christian, Johnny, Steve)?
Thanks for your help! I've been going crazy trying to figure this out.