Access query to promote students to next class

[mike60smart]

Hi

In the attached I added the Form to manage a Students Information.

You select a Student using the Combobox in the Form Header
Enter Relatives details
Select School Year and Grade
Select Term


Select Subjects for the Term selected.

This is just 1 way to manage the students details.

[erickemboi]

Perfect. Now assist me with query to promote Students in tblGrades from FORM 1 TO FORM 2 AND FROM FORM 2 TO FORM 3 AND FROM FORM 3 TO FORM 4. STUDENTS LEAVING FORM FOUR TO BE ARCHIVED.

[erickemboi]

I like your input. I am stuck on how to generate reports based on this form

[mike60smart]

See qryUpdateGrades in the attached.

As for a report you need to first create a query with all relevant fields required and then base your Report on the query.

[erickemboi]

Input the streams

[erickemboi]

I was thinking of creating another field in subjects table and put yes/no. A student can take maximum of 11 subjects while others can take 7 subjects. I am defeated on how to create a query for subjects selected in yes or no. I will really appreciate if you assist ne

[mike60smart]

You are not understanding the 1 to Many scenario

When you select a Student - Year & Grade - Term - Subjects

The subjects selected for a specific Student can be 1 or as many as you want.

You do NOT need a Yes/No field at all

[erickemboi]

Oh ok. Kindly please assist me with reports for Students and subjects only e g
Rank Student name subjects(based on selected subjects).


I will really appreciate

[mike60smart]

It is normal for you to make some effort in creating as this is the only way you will learn.

I think I have helped as much as I can.

Luck with your project.

[erickemboi]

You have really helped me. I do appreciate it so much. I have learned normalization tables from you and 1 to many relationships. I have gained alot from you. Thanks.

[erickemboi]

Kindly please i am trying to add another tblExaminationTypes for ExaminationTypes being CAT 1, CAT 2 AND END TERM for each term that is TERM 1, TERM 2 and TERM 3 for every year. I am defeated on relationships whether it will be related to tblYearTerms or it will be related to tblTermSubjects. Kindly assist me please. And how will I relate it to the main form??????

[mike60smart]

So for Each Term there are Specific Examinations (CAT1,Cat2 or End Term)

So the Students in Term1 will all sit Examination CAT1
So the Students in Term2 will all sit Examination CAT2
So the Students in Term3 will all sit Examination END TERM

When the Students sit these Examinations are there set Subjects??

Please give examples for Each Cat

[erickemboi]

Term 1 Student will sit for:-
CAT 1
CAT 2
END TERM
Term 2 Student will sit for:-
CAT 1
CAT 2
END TERM
Term 3 Student will sit for:-
CAT 1
CAT 2
END TERM
Probably a student can also have CAT 1
CAT 2
CAT 3
CAT 4
CAT 5 in a term.

[mike60smart]

OK so a Student in Term1 could sit CAT1 through to CAT5

And for Each CAT they sit a specific number of Subjects.

Are the Subjects Fixed for Each CAT ? or are they variable?

[erickemboi]

Subjects are fixed as per selection