MS Access - Full Questions and Answers

Objectives: MS Access - Full Questions and Answers

MS Access - Full Questions and Answers

Microsoft Access — Complete Questions and Answers

These questions and answers cover theory, practical tasks, long scenario-based cases, and project-based applications. Every answer is written in detail so that a learner or examiner can understand without any prior knowledge.

1. Theory Questions and Answers

Q1. What is Microsoft Access?

Microsoft Access is a Database Management System (DBMS) developed by Microsoft. It helps users create and manage databases. A database is a structured way to store information. In Access, you can use tables to store data, queries to search and analyze data, forms to enter data, and reports to present data.

Example: A school can use MS Access to store student information such as names, classes, and marks. Instead of using books and papers, all information is saved in the database for quick retrieval.

Q2. Name and explain the six main objects in MS Access.

1. Tables — store raw data in rows and columns. Example: Student table with Name, Age, and Class.
2. Queries — allow you to search, filter, or calculate from tables. Example: Find students who scored above 80.
3. Forms — provide user-friendly screens for entering or editing data. Example: A form to enter student marks.
4. Reports — produce printable outputs of data. Example: Student report card.
5. Macros — automate simple actions like opening a form or running a query.
6. Modules (VBA) — allow advanced programming for special tasks.

Q3. What is a Primary Key and why is it important?

A Primary Key is a field that uniquely identifies each record in a table. No two records can have the same primary key value.

Importance:
  • It prevents duplicate records.
  • It makes searching faster.
  • It is used to create relationships between tables.
Example: In a Student table, StudentID can be the Primary Key so each student is uniquely identified.

Q4. Explain Referential Integrity with an example.

Referential Integrity ensures that data entered in related tables is consistent.

Example: If we have a table Students and another table Marks, we cannot enter a mark for a StudentID that does not exist in the Students table. This prevents orphan records.

Q5. What are the advantages of MS Access over Excel?

  • MS Access allows linking multiple tables using relationships, while Excel is flat.
  • Access enforces Primary Keys and Referential Integrity.
  • Access supports forms and reports, which Excel does not provide natively.
  • Access handles larger datasets more efficiently than Excel.

2. Practical Questions and Answers

Q1. Create a table named Employees with fields EmployeeID, Name, Department, Salary.

Steps:
  1. Open MS Access → Create → Table Design.
  2. Add fields:
    • EmployeeID → AutoNumber (set as Primary Key)
    • Name → Short Text
    • Department → Short Text
    • Salary → Number
  3. Save the table as Employees.
Now you can enter employee records in this table.

Q2. Create a query to list employees earning above 500,000.

Steps:
  1. Click Create → Query Design.
  2. Add the Employees table.
  3. Double-click fields: Name, Department, Salary.
  4. In the criteria row for Salary, type: >500000.
  5. Click Run (red exclamation mark). Results show employees earning more than 500,000.

Q3. Create a form for entering employee details.

Steps:
  1. Create → Form Wizard.
  2. Select the Employees table and include all fields.
  3. Click Finish. The form appears for easy data entry.

Q4. Generate a report showing total salary by department.

Steps:
  1. Create → Report Wizard.
  2. Select Employees table and choose fields: Department, Salary.
  3. Group by Department.
  4. In summary options, select Sum of Salary.
  5. Finish. The report displays departments with their total salary expenditure.

3. Scenario-Based Long Questions

Q1. A school wants to manage student details and exam results. Explain fully how to design this database.

Step 1: Tables
  • Students: StudentID (PK), Name, Class
  • Subjects: SubjectID (PK), SubjectName
  • Marks: MarkID (PK), StudentID (FK), SubjectID (FK), Marks
Step 2: Relationships
- Students.StudentID → Marks.StudentID
- Subjects.SubjectID → Marks.SubjectID

Step 3: Queries
  • Failing Students: Marks < 40
  • Top Students: Highest Marks
  • Average Marks per Subject
Step 4: Forms — For teachers to enter marks.
Step 5: Reports — To print student report cards grouped by class.

Q2. A hospital wants to track patients, doctors, and appointments. Design and explain how forms and reports will be used.

Tables:
  • Patients: PatientID (PK), Name, Age, Address
  • Doctors: DoctorID (PK), Name, Specialization
  • Appointments: AppointmentID (PK), PatientID (FK), DoctorID (FK), Date, Time
Forms: One for patient registration, one for doctor schedules, and one for booking appointments.
Reports: Daily appointment list, bills for patients, doctor treatment history.

Q3. A shop owner wants to know which products are selling the most. How would you design queries and reports?

Tables:
  • Products: ProductID (PK), ProductName, Price
  • Sales: SaleID (PK), ProductID (FK), Quantity, Date
Query: Use SUM of Quantity grouped by ProductID.
Report: Show top-selling products sorted in descending order.

4. Project-Based Questions (with Profitable Ideas)

Project 1: School Management System

- Manage students, teachers, subjects, and marks.
- Automates report cards and saves time.
- Schools may pay for installation or subscription.

Project 2: Inventory & Sales Management

- Track stock, suppliers, and sales.
- Produces profit/loss analysis.
- Shops avoid losses and pay to use the system.

Project 3: Hospital Patient Record System

- Manages patients, doctors, appointments, and bills.
- Saves paper and prevents double-booking.
- Hospitals benefit from accurate data and may pay monthly fees.

Project 4: Microfinance Loan Management

- Track loans, repayments, and balances.
- Prevents fraud and errors.
- SACCOs and banks are willing to purchase or subscribe.

Project 5: Real Estate Rental Management

- Manage tenants, rent, and payments.
- Generates reminders and balances.
- Landlords can pay fees for easy management.

Reference Book: N/A

Author name: SIR H.A.Mwala Work email: biasharaboraofficials@gmail.com
#MWALA_LEARN Powered by MwalaJS #https://mwalajs.biasharabora.com
#https://educenter.biasharabora.com

:: 1::

⬅ ➡