Consolidate records in ACCESS

[Guest]

I am trying to consolidate records in a query. I have a table of student
ID's and course information. Some of the students are taking more than one
course. I would like to consolidate the records so that each record contains
the student ID along with all the course information. For instance, I might
have as fields: Student ID, course1, grade 1, course2, grade2, course3,
grade3. How can I do this in ACCESS? Any help would be so much appreciated!

 

[Jeff Boyce]

The data model you are describing (each record with one student field,
unknown number of course-grade paired fields) sounds like ... a spreadsheet!
If you want to use the strengths of Access, look into normalizing your data.

Interestingly enough, you've also provided the basic description you need to
normalize your data. One table holds Student info (name, DOB, etc.).
Another table holds Course info (Title, CourseNumber, etc.). A third table
holds valid combinations of Student and Course (which student took which
course), along with info relevant to that fact (i.e., on what date, with
what final grade, ...).

 

[Nikos Yannacopoulos]

Do not do this! It's bad design, and will create all sorts of problems:
increased storage, development difficulties, bad performance etc. The way to
handle this is to use three tables, one for the students, like:

tblStudents
StdID (PK)
LastName
FirstName
...
ect

a second one for courses, like:

tblCourses
CrsID
CourseName
...
etc

and a third one for courses and grades by student, like:

tblStudentCourses
ID (PK, Autonumber)
StdID (join to table tblStudents)
CrsID (join to table tblCourses)
Year
Semester
Grade
...
etc

HTH,
Nikos

I am trying to consolidate records in a query. I have a table of student
ID's and course information. Some of the students are taking more than one
course. I would like to consolidate the records so that each record contains
the student ID along with all the course information. For instance, I might
have as fields: Student ID, course1, grade 1, course2, grade2, course3,
grade3. How can I do this in ACCESS? Any help would be so much

appreciated!