CODEWITHSUNDEEP
sqlms-access
user7865035
user7865035

Reputation: 1

How to use microsoft access or sql pivot

I would like to use microsoft access or query to change from table1

student name    course_attended date    quiz score
john            course 1    3/1/2017    98
carl            course 1    3/1/2017    90
george          course 1    3/1/2017    77
john            course 2    3/5/2017    99
carl            course 2    3/5/2017    96
george          course 2    3/5/2017    80
john            course 3    4/4/2017    77
carl            course 3    4/4/2017    80
george          course 3    4/4/2017    85

to table2

student name    course 1    course 2    course 3
john            3/1/2017    3/5/2017    4/4/2017
                98          99          95
carl            3/1/2017    3/5/2017    4/4/2017
                90          96          89
george          3/1/2017    3/5/2017    4/4/2017
                77          80          85

Basically, with select distinct [student name] use transformation and pivot to change from table1 to table2. Please help. I would be greatly appreciated it.

Upvotes: 0

Views: 682

Answers (2)

user7865035
user7865035

Reputation: 1

I tried this, TRANSFORM First([date_a] & Chr(13) & Chr(10) & [quiz_score]) AS Data SELECT student_name FROM tbl_quiz GROUP BY student_name PIVOT course_attended;

and the query does not show the quiz_score. However, the form created from the query result shows it. Thanks a lot.

Upvotes: 0

June7
June7

Reputation: 21370

If you don't need to do any calcs on the output, try:

TRANSFORM First([attend_date] & Chr(13) & Chr(10) & [quiz_score]) AS Data
SELECT student_name
FROM Tablename
GROUP BY student_name
PIVOT course;

Date is a reserved word. Should not use reserved words as names. Also, avoid spaces and special characters/punctuation (underscore only exception) in names.

Upvotes: 1

Related Questions