Help! Question with assigning values and unmatched records

[maliciousdemon]

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.

 

[JS]

You kind of answered your own question. You need the one-
to-many relationship.

What I would do is create a table with every student name
in it. I would then create a make-table-query that links
the student record in the master list to the tblMath, to
the tblScience, and to the tblEnglish student records.

When you link the tables, make sure you choose the
linking type (double click on the line that links the
fields) that has the arrow pointing to the
tblMath/Scient/English tables. This denotes the one-to-
many relationship.

 

[Tim Ferguson]

I would then create a make-table-query that links
the student record in the master list to the tblMath, to
the tblScience, and to the tblEnglish student records.

Nononononono: you store data in fields, not in table names!

Tim F

 

[Guest]

Not entirely sure where you think I'm storing data in
table names. The tblEnglish/tblScience/tblMath will all
have the student key, which is what should be linked
(student key in the master list table to the student key
in the subject tables).

Malicious demon is basically already there, he just needs
to change the 1-to-1 relationship between his tables to 1-
to-many.

 

[Tim Ferguson]

Not entirely sure where you think I'm storing data in
table names. The tblEnglish/tblScience/tblMath

Storing the test subject in the table name. The English/ Science/ Maths etc
is an attribute of the Test, not the other way round.

Tim F