Populate Junction Table related to 3 Tables

[Daoud1987]

Hi !




I explain , i have 5 tables :


Class ( classID , classname,... )
Student (studid , firstname, lastname , classID ,....)
Course( courseid , coursename )
Term ( termid , termname , begindate , enddate )
Score ( scoreid , studid , courseid , termid , score)


Note :
1) A class can contain one or more students (one-to-many between Class and Student tables)


The table "Score" is a junction table between three tables : Student , Course and Term because it contains three foreign keys ( I could use a combination of 3 foreign keys to make a primary key ! ) .


The tables ; "Class", "Student", " Course ", "Term " already contain data for each table I created a data entry form .


My biggest problem is how to create a form to enter students' grades or scores for each student that belong to his class.


I do not know how to do it especially since the idea is that :


On a form I would like to use ComboBox to select a class that displays student's list from class selected and a ComboBox in same form to select course and another ComboBox to select a term and then enter grades or scores for each student


This is my general idea to enter students' grades.


The rules are :


A class contains one or more students
Each student takes one or more subjects.
Each student gets scores for each subject and each term(quarter)


Thank you very very much in advance

[June7]

So your business process is to create the records in Score table that associate Student, Course, Term and then go back to those records later to enter the grades?

Then you need some way to filter the dataset to particular Course and Term?

One way is with a parameterized query. Review:
http://www.datapigtechnologies.com/f...tomfilter.html
http://www.datapigtechnologies.com/f...earchform.html

Also sounds like you need dependent (cascading) comboboxes:
http://www.datapigtechnologies.com/f...combobox2.html

[Daoud1987]

Thank you very much
Yes what I want is to :

- select a class that will display the list of students who belong to this class named for example cboClass
- select a subject from another combobox named for example cbosubject
- select a term (quarter) from a combobox named for example cboTerm

why? because my junction table contains the following fields:
Score ( scoreid, #studentid, #subjectid, #termid, test1, test2, finaltest )
scoreid is the primary key for the score table and other fields: studentID, subjectid, termid are foreign keys
So to fulfill this junction table I did not need foreign keys but I need data on these key because the end user can not remember the numbers to make them into the table but it needs:


-the names of students who belong to a particular class
-the names of subjects for which the student has been tested
-names of terms (quarters) in which the tests were doing

So i have to combine 5 tables : Class , Student , Subject , Term , Score

Note : The list i want to have is something like this :

studLastName studFirstName Test1 Test2 FinalTest

Thank you in advance

[orange]

See this model it may give you some iinsight

[Daoud1987]

Hi Thank you very much for your response
But my problem is not about database structure or schema or about normalization my problem is how to enter data in junction table named Score , that's it

Thanks in advance

[June7]

Did you try techniques in the posted links?