Others

Add Academic Years to an Existing Microsoft Access Database

Adam DavisAdam Davis Nov 22, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Create the academic-year table

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.

2
Link students to years

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.

3
Define relationships

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.

4
Connect year-specific records

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.

5
Filter forms and reports

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.

Add academic-year and student-year tables in Access
Preserve existing records: Do not assign a year to historical records by guesswork. If a record's year is unknown, resolve it from reliable source data before relying on year-filtered reports.
Free Microsoft Office alternative

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.

Review and filter exported student records by academic year.Open and edit Microsoft Excel XLS and XLSX files.Use a familiar spreadsheet interface for a smoother transition from Microsoft Office.Work with a free, lightweight Office suite.
microsoft office alternative - wps office

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.