Convert row with columns of data into multiple rows?

[wittybrent]

I have two tables...

TABLE A:


TABLE B:


I will display the following values in report or in result table in query:

StudentNumber: 131003
Name: MABAG, MANUEL B.

Subject Unit Days Time Room


AB1AA-ENG 101 3
AB1AA-COMP 101 3
...

Thank you for the help???

[wittybrent]

I have the following table:

TABLE A:

StudentNumber	Subject1	Subject2
11111	AAA	BBB
22222	AAA	BBB

how will i display the multiple values in row into one column?
RESULT TABLE:

StudentNumber	Subject
11111	AAA
11111	BBB
22222	AAA
33333	BBBB

[June7]

This requires a UNION query. There is no wizard for UNION. Must type (or copy/paste) in the SQL View of query designer. This will essentially rearrange the data into the structure it should have been in.

SELECT StudentNumber, Subject1 AS Subjects FROM Registration
UNION SELECT StudentNumber, Subject2 AS Subjects FROM Registration;

Then join the UNION query to the SubjectSched table.

[wittybrent]

thanks for the help... appreciate so much... i tried the UNION but i used INNER JOIN... thanks again...

[June7]

Ooops! I forgot to use UNION in my query example. Take another look at the corrected post.

[wittybrent]

Thank you... June7... it helps me a lot... I figure out other query codes by using INNER JOIN....

here is my code,, it works already... thank you...

SELECT StudentNumber, LastName & ", " & FirstName & " " & MI AS NAME, Section, SectionSubjectCode, Unit, Days, Time, Room
FROM SubjectSched INNER JOIN Registration ON Registration.Subject1=SubjectSched.SectionSubjectC ode Or Registration.Subject2=SubjectSched.SectionSubjectC ode Or Registration.Subject3=SubjectSched.SectionSubjectC ode Or Registration.Subject4=SubjectSched.SectionSubjectC ode Or Registration.Subject5=SubjectSched.SectionSubjectC ode Or Registration.Subject6=SubjectSched.SectionSubjectC ode Or Registration.Subject7=SubjectSched.SectionSubjectC ode Or Registration.Subject8=SubjectSched.SectionSubjectC ode Or Registration.Subject9=SubjectSched.SectionSubjectC ode;


thank you po...

[June7]

I have never seen a join clause like that. I never would have considered it and don't understand how it can work but if you are getting what you want then congratulations.