How to Promote Students to next Class in a Database- Ms Access Example

While designing a database for students, you may have come across various templates, which in my opinion are quite lacking in important features. One of these is that students are in one level, and then they graduate, or are promoted to another class.

In my implementation, I'm going to show a sample of how this promotion can be done.

I have a studentClass table which has fields... SchYear, StudentID/ Adm, ClassID, StreamID.

The table indexes are such that one student can only be in one class and year once... Let me explain. StudentID and SchYear are set as unique. Ms access table in design view, click indexes. You will already see the primary key as an index. Then add another index give it any name eg studentClass Select the fields StudentID and SchYear below it. (SchYear wont have an index name) then set the property as unique.



 Back to the key information,

The idea is to create a query that selects students in the current, or previous year who are in form 1, 2,3 and 4, Re-Insert them into the table with the SchYear+1, ClassID+1 and the same Stream. This is done through an Insert/Append query.

To accomplish this you need a parameter... I chose to use YearID... The user selects the year to promote students to. eg. To promote students to Year 2017, There's a combo box which shows SchYear. The user selects 2017 and clicks a button. That button runs the query as described, ..

INSERT INTO StudentClass ( SchYear, StudentID, ClassID, StreamID...)
SELECT StudentID, StreamID From.... WHERE..., Name of the combo box +1 AS SchYear, ClassID +1 AS ClassID....