I really need help on this, and will be appreciate if some inputs can be
provided:

I have a student record database with the followings:

- Fields: student_name, math_score, math grade, english_score,
english_grade, science_score, science grade
- A student can take anywhere from 1 - 3 tests
- If a student has taken a test, a alphabetical grade will be assigned
to xxx_grade (where xxx represents subject)
- If a student hasn't taken the test, xxx_score will be empty and
xxx_grade will be empty (where xxx represents subject)

- Each class have a different grading scale. For example, a 85 in
English class can mean "A", but a 85 in Math can mean "B+"

Thus, I created the following tables:

TblStudentRecord
(student_name, math_score, english_score, science_score)

TblMathGrade
(math_score, math grade)

TblEnglishGrade
(english_score, english_grade)

TblScienceGrade
(science_score, science_grade)

I then use a "create table query" and link TblStudent
Record(math_score) to TblMathGrade(math_score), then TblStudent
Record(english_score) to TblMathGrade(english_score), etc. By doing so,
I hope to create a new table, with the following information:

TblNewStudentRecord
(student_name, math_score, math grade, english_score, english_grade,
science_score, science grade)

Nonetheless, it is not working. I origially have 500 records in
TblStudentRecord. After I run the query, it created a table with only
200 records. By somehow, it is only showing students that have taken
all 3 tests.

I am not sure did I do anything wrong here. Did I define the relations
wrong? I mean the relations between the table 1-to-1.

Your help will be greatly appreciated.

------------------------------------------------