union query or other way?

[stormin_norm]

I have a student table, class table, syllabus table, and grade table. They all must be linked to show the students grade on a form.
Note: Each class has different syllabus (list of courses) so for Mary Jane, she may have a different list of courses.

I can easily get the grades for the classes the student has taken (in syllabus). How can I get all the syllabus courses for his class, and show what his grades are, and allow updates to his grades.

sample output

John Smith
Class: J1135
Courses:
HIS101
HIS201
HIS301
ENG101
ENG102 A
ENG103
PHY101 B
PHY200 P

I tried different combos of joins, but access keeps yelling about incompatible joins.

 

[Gootz11]

the best thing you can do is have the courses listed as column headings and not rows. so your table would look like this

student_____class_____His101___His102____His103
J Smith______J1135______A_______B_________A__

doing this will allow you to show all records for that student in a single row. if you have many of this records in a table bring them into excell and use the transpose function to turn the columns into rows and then import them into access.
Good Luck
J

 

[stormin_norm]

Good idea, but it won't look good on the screen.

I guess the only thing to do is write a vba routine to get all courses, then for each course lookup for the student, and then print result.

I was hoping I could do a union.

Thanks.