moving Data

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

 

[Joseph Meehan]

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

I don't know if I want to really suggest this, but in reality you should
have two tables.

Table one is Name and includes the name and any other information about
the student you want to keep, like phone number address etc.

Table two would be grades That would include the subject and grade and
likely the year or quarter or date completed.

How they show is not related to how Access stores them. In the table
design you don't worry about display.

Display is done in reports and forms. I believe you can get Access to do
what you want in a limited fashion, but I will leave that for someone who
may have worked with that.

 

[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

 

[Guest]

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

 

[Joseph Meehan]

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

Most of those who answer questions here, have been down that same road
ourselves. We also thought it would be easier not to do it right and we
have found out time and time again (more for the more dense among us, like
me) that it is easier to do it right than to try and fix something that is
broken because of bad table design.