moving Data

M

Max

In an access table, I have a large amount of data in multiple rows and I
want to move the data from rows to columns. The amount of data is too large
to cut and past. How can this be done?
 
M

Max

For example

ID name Subject Grade
111 Max Math A
111 Max ENG C

I would Like them to show as :

ID Name Subject1 Grade
Subgect2 Grade
111 Max Math A
ENG C

Please Help
 
M

Max

Example:

ID name Subject1 Grade
111 Max Math A
111 Max ENG C

I would Like them to show as :
ID name Subject1 Grade Subjec2 Grade
111 Max Math A ENG C
 
P

Pieter Wijnen

Create a table STUDENT w fields:
StudentID autonumber
Student Text(50)
....

Create a table SUBJECT
SubjectID autonnumber
Subject text(20)

Create a table STUDENTSUBJECT
StudentID long
SubjectID long
Grade Text(1)

create relationships between the tables
input into Students values (1,'Fred') & (2,'Wilma')
input into Subject values (1,'Math') , (2,'English') & (3,'Sports')
input into StudentSubject values (1,1,'A'),(1,2,'B'),(2,1,'C') & (2,2,'A')

Create a Query selStudentSubject:
SELECT Student.Student, Subject.Subject, StudentSubject.Grade
FROM Subject INNER JOIN (Student INNER JOIN StudentSubject ON
Student.StudentID = StudentSubject.Student) ON Subject.SubjectID =
StudentSubject.Subject;

Create a (crosstab) query ctStudentSubject:
TRANSFORM First(selStudentSubject.Grade) AS FirstOfGrade
SELECT selStudentSubject.Student
FROM selStudentSubject
GROUP BY selStudentSubject.Student
PIVOT selStudentSubject.Subject In ('Math','English','Sports');

and hey presto!

hth

Pieter
 
M

Max

Thanks, but my tables are already set up and it's crazy to start from
scartch. Thanks any way
 
Top