I was wondering if anyone could offer a solution to this database design problem. I have very little experience with relational databases in Access. I have only used databases just to get easy data, but now it is starting to get complicated. I read a little on relational database design, but I keep coming into roadblocks with this one. In Access, you can only assign 1 primary key, so I assign this key to the autonumber column because in my other tables it is impossible for me to not have duplicate values.

See, the app I am creating is an online grade book for teachers. Each teacher has several classes, each class has a number of students, each student has a number of assignments, each assignment has a category (homework, classwork, tests, etc.), each assignment has a grade.

This app allows teachers to create a class. The program generates a unique class ID. Then the teacher enters each student one at a time, and the program generates a unique student ID. All four pieces of data are entered into the database.

What I have done is created the following tables. Following the table is the columns, and a small snippet of how it is set up as an example.

Table 1 (Roster): ID--TeacherID--ClassID---StudentID
1 123 234 567
2 123 234 678
3 123 234 789
4 123 235 987
5 123 235 876
6 124 236 567
7 124 236 678
8 124 237 789
9 124 237 987

Notice how each teacher 123, 124 may have the same students but in different classes.

Table 2 (Scores): ID--StudentID--AssignmentID--Score
1 567 000 89
2 678 000 98
3 789 000 90
4 567 001 90
5 678 001 78
6 789 001 85
This table records the student's grade to a particular assignmentID. Not having duplicate values is also not possible, that is why I have the ID column. This table is probably the largest because it records every students scores for each assignment they have done.

Table 3 (Weight): ID--ClassID--CategoryID--Assignment--AssignmentID
1 234 Homework p. 64 000
2 234 Classwork p. 78 001
3 235 Homework p. 34 003
4 235 Homework p. 64 078
5 236 Test Spelling 089
6 237 Classwork p. 120 034
7 237 Test Vocab 064

This table records the assignments teachers give their students, the category, and the classID associated with the assignment. Each assignment is given a unique ID because teachers might give an assignment the same name. So in this table ID and AssignmentID are unique.

The problem I am having is how to write a particular SQL command.
With the tables above, how can I get all the grades for each student in a particular class. I will place this data in a datatable and store it in a dataset and it will be displayed as follows:

Class 234

StudentID "p. 64" "p.78"
567 89 90
678 98 78
789 90 85

Basically, how do I join these tables efficiently, and did I design this database correctly? Any other suggestions on how to solve this problem will be greatly appreciated.