Add Academic Years to an Existing Microsoft Access Database
Question details
Add support for multiple academic years to an existing student database without creating a new database each year.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A student database must retain historical information while supporting new years in enrollments, queries, forms, and reports.
- Observed behavior
- Students and their year-specific records should remain available across academic years, with forms and reports filterable by year.
Review the existing student, enrollment, course, and attendance tables in Access to identify their primary keys and any fields that already record an academic year. Determine how existing records will be assigned to verified years before changing relationships or filters.
Add academic-year and student-year tables in Access
Use related tables to record each student's participation in multiple years and filter year-specific information.
Keep one student record per student and store year-specific participation in a junction table. Existing enrollment records can serve this role if they already contain the necessary student and year references.
In Access, select Create > Table Design. Add AcademicYearID as an AutoNumber primary key and a year label such as 2025–2026, then save the table as AcademicYears.
Use Create > Table Design to create a StudentYears table with a primary key, StudentID, and AcademicYearID. Match each foreign key's data type to its referenced primary key; for an AutoNumber key, use Number with Field Size set to Long Integer.
Select Database Tools > Relationships, add Students, AcademicYears, and StudentYears, and connect each primary key to its corresponding StudentYears field. Enable Enforce Referential Integrity where the existing data permits it. In StudentYears Design View, use Indexes to create a unique composite index on StudentID and AcademicYearID.
In Table Design, link enrollment, course, and attendance records to the appropriate AcademicYears or StudentYears record, according to what each row represents. Assign existing records to verified years and update affected queries and forms to use the new relationships.
Create a year selector on a form, then open a report's source query in Design View and add a criterion referencing the selector, such as Forms![frmYearPicker]![cboAcademicYear]. Keep the selector form open when running the report, and test both a historical year and a newly added year.

Review Exported Student Data with WPS Spreadsheets
WPS Office is a free, lightweight Office alternative with a familiar interface and Microsoft Office format compatibility. Export Access data to Excel for easy review in WPS Spreadsheets; WPS does not replace Access relationships, forms, or reports.

Frequently Asked Questions
Do I need a separate Access database for each academic year?
No. Add each year as a row in an AcademicYears table and link students and year-specific records to it. Existing years can remain in the same database.
How can one student be registered for several years?
Add one StudentYears row for each student and academic-year combination. Create a unique composite index on StudentID and AcademicYearID to prevent duplicate combinations.
How do I show a report for only one academic year?
Add a year selector to a form and reference its control in the report's source query criteria. Open the selector form and choose a year before opening the report.
What if my enrollment table already stores academic years?
Inspect it in Table Design and Database Tools > Relationships. If it already links each student to an academic-year record, it may serve as the junction table without creating a separate StudentYears table.
Can WPS Office edit the Access database relationships?
No. Use Microsoft Access to maintain the database relationships, forms, and reports. You can export table or query results to an Excel file and open that file in WPS Spreadsheets.




