MS Access Final Project

The application should allow an institute to:

• Register and manage students.
• Maintain course information.
• Enroll students in courses.
• Record fee payments.
• Calculate outstanding course fees.
• Search and filter student records.
• Generate student and fee reports.
• Navigate using a main menu.
• Automate common actions using macros.
• Create and test database backups.

1. Open Microsoft Access.
2. Select Blank Database.
3. Enter InstituteManagement.accdb.
4. Select a suitable folder.
5. Click Create.
6. Create the tables described below.

Use meaningful names for all tables, queries, forms, and reports.

Save the table as Students.

Instructions

1. Open Create → Table Design.
2. Add the fields listed above.
3. Set StudentID to AutoNumber.
4. Set StudentID as the primary key.
5. Save the table as Students.

Save the table as Courses.

Sample Courses

The Enrollment table connects students with their selected courses. Save it as Enrollment.

A separate enrollment table allows students to take multiple courses and each course to have multiple students.

Save the table as FeePayments.

Each payment should be stored as a separate transaction. This supports installments and payment history.

Students.StudentID
        |
        | One-to-Many
        v
Enrollment.StudentID

Courses.CourseID
        |
        | One-to-Many
        v
Enrollment.CourseID

Enrollment.EnrollmentID
        |
        | One-to-Many
        v
FeePayments.EnrollmentID

Steps

1. Open Database Tools → Relationships.
2. Add the four tables.
3. Connect Students.StudentID to Enrollment.StudentID.
4. Connect Courses.CourseID to Enrollment.CourseID.
5. Connect Enrollment.EnrollmentID to FeePayments.EnrollmentID.
6. Enable Enforce Referential Integrity where appropriate.
7. Save the relationship layout.

Create the following forms using Form Wizard or Form Design:

• frmStudents — add and edit student details.
• frmCourses — maintain course information.
• frmEnrollment — enroll students in courses.
• frmFeePayments — record fee installments.

General Steps

1. Select the appropriate table.
2. Open the Create tab.
3. Choose Form or Form Wizard.
4. Select the required fields.
5. Choose a suitable layout.
6. Save the form with the suggested name.
7. Open the form and test adding a record.

Create a query that displays each student's name and the course in which they are enrolled.

SQL View

SELECT
    Students.StudentID,
    Students.StudentName,
    Courses.CourseName,
    Enrollment.EnrollmentDate
FROM
    (Students
    INNER JOIN Enrollment
        ON Students.StudentID = Enrollment.StudentID)
    INNER JOIN Courses
        ON Enrollment.CourseID = Courses.CourseID
ORDER BY
    Students.StudentName;

Steps

1. Create a new query.
2. Switch to SQL View.
3. Enter the SQL statement.
4. Click Run.
5. Save the query as qryStudentEnrollment.

The fee dues query compares each enrollment's course fee with the total payments recorded for that enrollment.

First, save the following query as qryEnrollmentPayments:

SELECT
    Enrollment.EnrollmentID,
    Students.StudentName,
    Courses.CourseName,
    Courses.CourseFee,
    Nz(Sum(FeePayments.AmountPaid),0) AS TotalPaid
FROM
    ((Students
    INNER JOIN Enrollment
        ON Students.StudentID = Enrollment.StudentID)
    INNER JOIN Courses
        ON Enrollment.CourseID = Courses.CourseID)
    LEFT JOIN FeePayments
        ON Enrollment.EnrollmentID = FeePayments.EnrollmentID
GROUP BY
    Enrollment.EnrollmentID,
    Students.StudentName,
    Courses.CourseName,
    Courses.CourseFee;

Next, create another query using the saved query as its data source:

SELECT
    EnrollmentID,
    StudentName,
    CourseName,
    CourseFee,
    TotalPaid,
    CourseFee - TotalPaid AS FeeDue
FROM
    qryEnrollmentPayments;

Save the second query as qryFeeDues. It calculates the remaining amount for each enrollment.

Create reports for reviewing and printing database information.

• rptStudentList — list of registered students.
• rptCourseList — list of available courses.
• rptStudentEnrollment — students and their courses.
• rptFeeDues — total fees, paid amounts, and outstanding dues.
• rptPaymentHistory — individual fee transactions.

Steps

1. Select a suitable table or query.
2. Open the Create tab.
3. Choose Report Wizard.
4. Select the fields to display.
5. Configure grouping and sorting.
6. Select a layout and orientation.
7. Save the report with a meaningful name.
8. Review the output in Print Preview.

The main menu allows users to open forms and reports from one central location.

INSTITUTE MANAGEMENT SYSTEM
-----------------------------------
[ Manage Students       ]
[ Manage Courses        ]
[ Student Enrollment    ]
[ Fee Payments          ]
[ Student Enrollment Report ]
[ Fee Dues Report       ]
[ Payment History       ]
[ Exit Application      ]

Steps

1. Create a blank form in Design View.
2. Save it as frmMainMenu.
3. Add a heading and command buttons.
4. Use the Button Wizard or macros to open forms and reports.
5. Test every button in Form View.
6. Optionally set frmMainMenu as the startup form.

Macros can automate routine actions without requiring you to write VBA for every operation.

Example 1: Open the Student Form

Macro Action: OpenForm
Form Name: frmStudents

Example 2: Display a Message

Macro Action: MessageBox
Message: Welcome to Institute Management System
Title: Welcome

Example 3: Connect a Button to a Macro

1. Open frmMainMenu in Design View.
2. Select a command button.
3. Open the Property Sheet.
4. Select the Event tab.
5. Configure the On Click event.
6. Save and test the form.

Validation rules help prevent invalid values from being entered into the database.

Examples

Course Fee

Validation Rule: >=0
Validation Text: Course fee cannot be negative.

Amount Paid

Validation Rule: >0
Validation Text: Payment amount must be greater than zero.

Required Student Name

Set the StudentName field's Required property to Yes and consider setting Allow Zero Length to No.

Create a backup after testing the completed application.

1. Close the database and ensure no one is using it.
2. Locate InstituteManagement.accdb.
3. Copy the file to a separate backup folder or storage device.
4. Give the backup a date-based name.
5. Open a copy of the backup and verify important objects.
6. Keep at least one backup separate from the original computer when practical.

InstituteManagement_Backup_2026-10-13.accdb

Use this checklist to track your project work:

1. What is Microsoft Access?
2. What is a primary key?
3. What is a foreign key?
4. Why are relationships important?
5. What is database normalization?
6. What is the difference between a table and a query?
7. What is the purpose of a form?
8. What is the purpose of a report?
9. What is a macro?
10. How can you calculate outstanding fees?
11. Why should phone numbers be stored as text?
12. Why is database backup important?