Show corresponding subjects for selected grade

[Khalil Handal]

Hi to all
I have two tables tblGrades and tblSubjects.
tblGrades shows grade names from grade 1 to grade 8
tblSubjects shows subject names for all the grades in school.
Some subjects like Math science are common for all grades and other subjects like Art is specific for some grades only and not found in other grades.

How can I show the subjects for the specific selected grade only?

In other words:
If I select Grade 8: Show subjects for Grade 8 only
If I select Grade 2: Show subjects for Grade 2 only

Any ideas?



Khalil

[Welshgasman]

You need a junction table.

[ArviLaanemets]

tblSchoolYears: SchoolYear;
tblGrades: Grade;
tblSubjects: SubjectID, SubjectName, ...;
and the junction table like
tblGradeSubjects: GradeSubjectID, Grade, Schoolyear, SubjectID, ....

Then you need either a single type form either based on tblGrades, and having an unbound control where you can select schoolyear, or unbound form with unbound controls where you can select grade and schoolyear.
In this main form you'll need a continuous subform based on join query from tblGradeSubjects and tblSubjects (like SELECT gs.Grade, gs.SchoolYear, s.SubjectName FROM tblGradeSubjects gs INNER JOIN tblSubjects s ON s.SubjectID = gs.SubjectID). The Grade field of subform will be linked with Grade field of main form, and Schoolyear field of subform will be linked with unbound schoolyear control in main form, or in case the main form is unbound one, then he Grade field of subform will be linked with unbound grade control in main form, and Schoolyear field of subform will be linked with unbound schoolyear control in main form.

As result, when you select the grade and schoolyear in main form, a list of all subject names (along with additional info from tblSubjects and/or tblGradeSubjects, in case you have such additional fields in those tables) will be displayed in subform. And whenever you add a record into subform (and select a subjectID for it from combo control based on tblSubjects, so user sees SubjectName instead of SubjectID), the new record will have automatically have same Grade and Schoolyear as in main form applied.

To avoid the same subject added to same grade in same schoolyear repeatedly, you need there additional unique index based on Grade, Schoolyear and SubjectID fields.

[Khalil Handal]

Hi,
Thank you very much for your reply.
I build the tables as suggested and I am doing progress. I still need to have the form ready.
I will let you know if I face more problems.
Thank you again for the detailed response.

Khalil

[Khalil Handal]

Hi,
I am trying to build the subform without success. The Sub Form should look like this:

Student Name English lang. Math .....(other subjects)
Jack 75 70
Patricia 88 80


The Student Names and subjects are selected based on the combo Boxes from the main form. The main form has the following combo boxes:
cboSchoolYear, cboGradeLevel, cboGradeSection.

I have the following tables:
tblStudents, tlkpGrades, tblGradeSection, trelGradeSubject, tblSubjects, tblJoinStudentsMarks.

tblJoinStudentsMarks has the following fields:
pkStudentMarks (primary key), Mark1, Mark2, fkStudent, fkGrades, fkSchoolYear, fkGradeSection

In order to add records to the table tblJoinStudentsMarks , The value for the fields: fkStudent, fkGrades, fkSchoolYear, fkGradeSection should be taken from the combo boxes on the main form and the user type the value of the two marks for each subject.

Any ideas?
Thanks

[Welshgasman]

I am trying to build the subform without success. The Sub Form should look like this:

Student Name English lang. Math .....(other subjects)
Jack 75 70
Patricia 88 80

That would be a crosstab query. Use the wizard.

tblJoinStudentsMarks has the following fields:
pkStudentMarks (primary key), Mark1, Mark2, fkStudent, fkGrades, fkSchoolYear, fkGradeSection

Wrong! One record per subject, not one file per subject.
What happens when you start a new subject? you have to amend the whole database.

[Khalil Handal]

Hi,
Based on my knowledge I cannot You can't enter data as a crosstab. I am not sure if I was clear! I want to input data not show an existing data.

Wrong! One record per subject, not one file per subject.

This means that my table tblJoinStudentsMarks do not have the correct design.
How would my table design looks like if it is going to be based on "on record per subject"?

Thank you
Khalil

[Welshgasman]

Based on my knowledge I cannot You can't enter data as a crosstab. I am not sure if I was clear! I want to input data not show an existing data.

That is correct.
See post 3 on how you would handle the data entry. Form and subform.

[Khalil Handal]

Hi,
I reviewed Post # 3 and created my tables.
My problem is that I am not able to link a combo box from the unbounded main form with a textbox (a control) on the bounded Sub Form.
How can I do this?

Thnaks

[ArviLaanemets]

Hi,
My problem is that I am not able to link a combo box from the unbounded main form with a textbox (a control) on the bounded Sub Form.

Linking subform to control in main form must be done manually - i.e. from properties window of subform control.

In Design mode of main form, with subrorm not activated jet, click on subform ONCE. As result, the subform control is activated (double-click or clicking it twice activates the form which is the source of subform control instead).
In Properties window, the properties of subform control are displayed.
In LinkParentFields property, the list of linked main form's source table/query fields, or linked main form's controls must be listed. Like "MainTableField1; MainFormControl2".
In LinkChildFields property, the list of linked subform's form source table fields must be listed (NB! Fields, not controls!). Like "SubformTableField1; SubformTableField2".

[Khalil Handal]

Thank you all for the replies.