0

I have a database table in MS Access that looks as this

Grade    Student_Name1     Student_Name2    Student_Name3     Student_Name4      
A        Joe               Mary             Alice             Harry              
B        Bill              Fred             Alex              Will               
C        Scott             John             Jill              Katie
D....

and so on,

I'm trying to transpose the data so it is in this format

Grade  Student_Name
A      Joe
A      Mary
A      Alice
A      Harry
B      Bill
B      Fred
B      Alex
B      Will
C      Scott
C      John
C      Jill
C      Katie
D....

Please suggest a way to do this MS Access.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
user3495451
  • 29
  • 1
  • 5
  • Check out UNION query. – Fionnuala May 14 '14 at 14:32
  • I'm quite new to Access and SQL programming, can you tell me a sample code? – user3495451 May 14 '14 at 14:35
  • The general idea with Stackoverflow is that you post what you have tried and people help you, try reading http://office.microsoft.com/en-ie/access-help/combine-the-results-of-several-select-queries-by-using-a-union-query-HA010206109.aspx – Fionnuala May 14 '14 at 14:40
  • The article tells about UNION to join values from different tables, I guess I have to try a JOIN statement to achieve the desired result. – user3495451 May 14 '14 at 15:06
  • You do not need join. You can union from the same table see http://stackoverflow.com/questions/736317/sql-to-transpose-row-pairs-to-columns-in-ms-access-database?rq=1 or http://stackoverflow.com/questions/5265270/sql-query-with-multiple-lookups/5265757#5265757 – Fionnuala May 14 '14 at 15:07

0 Answers0