Update table question

[kirtney]

Guys i am building a database as a school project. It is based on a grading system that would allow teachers to store and update student examination marks. What i would like to know, is there a way i can set up the database to automatically promote students from one academic year to another? And if it can be done how do i do it. ( I was told that a macro may be able to help me with this functionality).

 

[Mj-Egerton]

is there a separate table for students in each year? or a field in a table which defines what year they are currently in?

 

[JANR]

is there a way i can set up the database to automatically promote students from one academic year to another?

How does the promotion happens i real life?? Is it once a year at the same time as in school start each autum.

Does some get promoted and others do not?

If the answer to the first question is yes, then DON'T store it in a table!!

The rule of the game is that IF you can calculate it you do not need to store the result, you only store the pieces you need for the calculation.

In this case if the school starts in the same month every year you can calculate the promotion something like this:

Function SchoolYear(AnyDate As Date) As Integer
   SchoolYear = DateDiff("m", AnyDate, Date) \ 12 + 1
End Function

Then to display a students current academicyear you put this in the controlsource of control on a form or a report:

=SchoolYear([StartDate])

The function goes in a Standard Module to be accessible throughout your application.

JR

 

[kirtney]

all the student information is stored in a table. they are all automatically promoted every year. All i really need the database to so is automatically promote each student from one class to another (for example from grade 11 to grade 12 ). Once this is done i can then assign classes to a group of students. Hope this providers a clearer picture

 

[JANR]

If thats the case then my reply stands as it is, you DON'T store the class. It is derived from the date the student starts, you only have to adjust for the starting classnumber marked in red in the function.

Function SchoolYear(AnyDate As Date) As Integer
   SchoolYear = DateDiff("m", AnyDate, Date) \ 12 + [COLOR=red]1[/COLOR]
End Function

If the starting class is 10 then put that instead of 1.

JR

 

[kirtney]

Thanks very much i will test it out