In my database, the key of the transition among the academic years is AcadimicYear table which include information about the academic unique ID, year name, start date and end date. To prepare my database to the next year, I need the AcadimicYear table move to the next year (record) automatically so that a new information about the new year is entered , also make that year valid for the whole database forms. Any idea to achieve this goal please?
Moving my database to the next acadimic year (1 Viewer)
- Thread starter AlefAelol
- Start date
Could you explain more please. An Academic year has many levels, many courses and many students. So if they are not tied to the academic year, then how organize them? they are all tied to the academic years.
Something like below:
I'm organizing it as follow:
A main form (AcadimicYear) of subfrom1(YearLevel) of subfrom2 (YearLevelCaption) of subfrom3 (YearLevelCaptionCourseTeacher)
Attachments
-
Capture.JPG
AccessBlaster
Registered User.
- Local time
- Yesterday, 16:11
- Joined
- May 22, 2010
- Messages
- 5,951
Are you saying the Academic Year overlaps, something like 2017-2018. Next would 2018-2019?
isladogs
MVP / VIP
- Local time
- Today, 00:11
- Joined
- Jan 14, 2017
- Messages
- 18,221
In a similar situation with my own school based apps, I have a routine predictably called UpdateAcademicYear
This goes through various tables running update queries to change records as appropriate including
1. Transfer leavers in Year 11 to archive tables
2. Transfer reporting and attendance data for year to archive tables
3. Increase AcYear by 1 e.g. from 2017-18 to 2018-19
4. Modify year group for student tutor groups e.g. 7A to 8A
5. Import new students and staff
6. Update course and class details and student/staff timetables
7. Archive old school calendar and replace with new
Etc, etc
The details will vary for each country and indeed each school but the idea is to create a routine which can just be run each year at the click of a button by an administrator.
It should also have safeguards so it can be undone in case of issues. Also to prevent it being run twice by mistake.
Setting all of this up is time consuming and should only be tested on backup copies of your data.
Do NOT use on LIVE DATA until you are SURE it works correctly. Even then BACKUP your data first.
If you ignore the above you will likely spend a huge number of hours sorting out data where part has been updated but not all.
Good luck
This goes through various tables running update queries to change records as appropriate including
1. Transfer leavers in Year 11 to archive tables
2. Transfer reporting and attendance data for year to archive tables
3. Increase AcYear by 1 e.g. from 2017-18 to 2018-19
4. Modify year group for student tutor groups e.g. 7A to 8A
5. Import new students and staff
6. Update course and class details and student/staff timetables
7. Archive old school calendar and replace with new
Etc, etc
The details will vary for each country and indeed each school but the idea is to create a routine which can just be run each year at the click of a button by an administrator.
It should also have safeguards so it can be undone in case of issues. Also to prevent it being run twice by mistake.
Setting all of this up is time consuming and should only be tested on backup copies of your data.
Do NOT use on LIVE DATA until you are SURE it works correctly. Even then BACKUP your data first.
If you ignore the above you will likely spend a huge number of hours sorting out data where part has been updated but not all.
Good luck
Yes, exactly
I got very nice ideas from your replay, just about this point
Should I fill the Academic Year in advance(let say for 10 years) or I may make it dynamic increasing ?
isladogs
MVP / VIP
- Local time
- Today, 00:11
- Joined
- Jan 14, 2017
- Messages
- 18,221
I have a function that does the update each year.
Only the current academic year value is stored at any time
Does your function delete the data after it been archived ?
isladogs
MVP / VIP
- Local time
- Today, 00:11
- Joined
- Jan 14, 2017
- Messages
- 18,221
The function I was referring to only updates the academic year value e.g. to 2018-19
The whole procedure consists of many thousands of line of code that each perform one part of the update. For easy maintenance it is split into several smaller procedures each dealing with specific sections of the task. See my original list for some of the ain sections. There are more than that in my database as it covers almost all aspects of a schools information management system
In general archived data is transferred (copied) to separate tables then deleted from the original. It's is NEVER just deleted.
An alternative approach is to keep the data in the same tables but mark it as archived using as Boolean field.
The whole procedure consists of many thousands of line of code that each perform one part of the update. For easy maintenance it is split into several smaller procedures each dealing with specific sections of the task. See my original list for some of the ain sections. There are more than that in my database as it covers almost all aspects of a schools information management system
In general archived data is transferred (copied) to separate tables then deleted from the original. It's is NEVER just deleted.
An alternative approach is to keep the data in the same tables but mark it as archived using as Boolean field.
How to get your list please ?
Sorry for bothering you, but I am new to this forum. may you post the link here, or just guide me to go to post #7 and your list
Last edited:
I see, sorry I got it wrong. Many thanks .
My database is roughly something as you suggested. In this case, how to move to the next record at the Academic year table ?so when user open the database, then the next year academic is shown and he/she has to fill the new year information
