Needs Help in DB Design

[siamandm]

Hello All,
i would like to have a database to enter degrees for each subject per a student name, for example for the college of science we have several departments ( IT , biology , chemistry .... )
when i write a student name and choose a department i would like it show the subjects belong that department as shown below ,

Capture.PNG

i think this may be a wrong way ... could you please advise a better way design ?
many thanks

[HansV]

Can the same subject be taught in multiple departments? For example, mathematics might be taught in all science departments.

[siamandm]

May be there is one or more subject taught in more than one department
Regards

[HansV]

Thanks, I'll get back to you.

[HansV]

Here is a sample database. Look at Database Tools > Relationships to see how the tables are related.

There is an After Insert event procedure in frmStudent to create the grade records for a new student.

(The subjects and students are fictitious and just meant as examples)

A real-life database might be more complicated.

Education.zip

[siamandm]

Thanks alot and much appreciated

[siamandm]

Here is a sample database. Look at Database Tools > Relationships to see how the tables are related.

There is an After Insert event procedure in frmStudent to create the grade records for a new student.

(The subjects and students are fictitious and just meant as examples)

A real-life database might be more complicated.

Education.zip

Dear Hans,
thanks a lot for the example,
i have tested but when i change the department the subjects are not changing accordingly , any advise please?
Regards

[siamandm]

Here is a sample database. Look at Database Tools > Relationships to see how the tables are related.

There is an After Insert event procedure in frmStudent to create the grade records for a new student.

(The subjects and students are fictitious and just meant as examples)

A real-life database might be more complicated.

Education.zip

Dear Hans,
thanks a lot for the example,
i have tested but when i change the department the subjects are not changing accordingly , any advise please?

for new student you can choose a department and you get the subjects accordingly , but after that when you change the department for already entered record the subjects are not changes
Regards

[HansV]

What would you like to happen when an existing student changes department? Should the grades for the "old" subjects be deleted, or should they remain?

[siamandm]

Yes, should be deleted

Thanks a lot

[HansV]

See the attached version. I have now used the After Update event of the Department combo box.

Education.zip