BELFAST INTERNATIONAL SCHOOL
KONTAGORA
[Link] PRACTICAL ON MS ACCESS
1. OVERVIEW OF MICROSOFT ACCESS
Microsoft Access is a Database Management System (DBMS) that combines the relational Access Database
Engine with a graphical user interface and software-development tools. Unlike Excel, which organizes data in
flat sheets, MS Access stores data in related tables, allowing users to handle massive amounts of information
efficiently, eliminate duplicates, and build powerful search tools.
Four Core Elements of MS Access:
• Tables: Where your raw data is securely stored in rows (records) and columns (fields).
• Queries: Tools used to search, filter, calculate, and extract exact information from tables based on rules
you set.
• Forms: Customized, user-friendly layouts used to easily enter, view, and edit records.
• Reports: Polished summaries designed to format, calculate, and print out finalized data cleanly.
Step-by-Step Fundamentals
A. Opening MS Access and Creating a New Database
1. Click the Start Menu on your Windows taskbar.
2. Type Microsoft Access and press Enter.
3. In the opening dashboard, click on Blank Database.
4. In the pop-up panel on the right, type your preferred database filename in the box.
5. Click the folder icon next to the filename to choose your save location, then click Create.
B. Creating a Table in Design View
1. Go to the top menu and select the Create tab, then click Table Design.
2. In the Field Name column, enter the name of your variable (e.g., StudentID).
3. In the Data Type column right next to it, select an appropriate format from the dropdown list (e.g., Short
Text, Number, AutoNumber).
4. To set a field as unique, right-click the row header next to that field name and click the key icon to assign it
as the Primary Key.
5. Click the Save icon (or press Ctrl + S), give your table a name, and click OK. Double-click the table name
in the left panel to switch to Datasheet View and enter your rows.
Belfast International School • ICT Practical Manual Page 1 of 7
C. Creating a Query using Wizard or Design View
1. Click the Create tab on the ribbon menu.
2. Click on Query Design (recommended for structural filtering).
3. A window will pop up showing your tables. Select the table(s) you need, click Add, and then close the
popup.
4. Double-click the specific fields from the table box to drop them down into the query layout columns below.
5. In the row marked Criteria under a specific column, type the rule you want to filter by (e.g., typing "Male"
under a Gender field).
6. Click the red exclamation mark icon (Run) on the top menu bar to view your filtered results.
2. PAST WAEC PRACTICAL QUESTION
QUESTION 1
1A. Using Microsoft Access, create the SUBJECT TABLE and DEPARTMENT TABLE as shown below.
Set the field size and data type of your choice.
SUBJECT TABLE
SubjectCodes SubjectNames DepartmentCodes
COM111 Computer Science COM10
xxx xxx xxx
xxx xxx xxx
xxx xxx xxx
xxx xxx xxx
MAT500 Mathematics MAT11
xxx xxx xxx
xxx xxx xxx
xxx xxx xxx
i. Enter any seven subjects of your choice to replace "xxx" in the "SubjectNames" Column.
ii. Use the first three letters of the "SubjectNames" entered in 1A(i) and add any number between
100-999 as SubjectCodes to replace the "xxx" e.g Computer Science as COM111 and Mathematics as
MAT500.
Belfast International School • ICT Practical Manual Page 2 of 7
iii. Use the first three letters of your SubjectNames entered in 1A(i) and add any number between
10-99 to replace "xxx" in the DepartmentCodes e.g Computer as COM10 and Mathematics as MAT11.
NOTE: No two SubjectCodes and DepartmentCodes should be the same.
DEPARTMENT TABLE
DepartmentCodes DepartmentNames HODNames Gender
Male
Male
Female
Male
Female
Female
Male
iv. Enter six names of your family members in HODNames column in DEPARTMENT TABLE.
v. Type/key every information provided in the DepartmentCodes and SubjectNames columns of the
SUBJECT TABLE into the DepartmentCodes and DepartmentNames columns respectively in the
DEPARTMENT TABLE.
vi. Create the following queries:
(a) Show all the female HODs in the school and print.
(b) Show the DepartmentName, SubjectCode, and SubjectName for the DepartmentCode MAT11
and print. (22 marks)
B i. State two precautions taken while performing the tasks in 1A. (2 marks)
ii. Define the following: (a) Primary key (b) Table (2 marks)
iii. List four examples of DBMS packages. (2 marks)
iv. What is a database? (2 marks)
3. SOLUTION GUIDE & ANSWERS TO QUESTION 1
Data Entry Design Setup
Before executing the steps, here is the populated data format layout designed to precisely satisfy the variable
replacement criteria (seven subjects chosen, three-letter codes, six family names mapped as HODs, and
matched department parameters):
Belfast International School • ICT Practical Manual Page 3 of 7
Populated SUBJECT TABLE Data
SubjectCodes SubjectNames DepartmentCodes
COM111 Computer Science COM10
ENG102 English Language ENG12
BIO204 Biology BIO14
CHM305 Chemistry CHM15
PHY401 Physics PHY16
MAT500 Mathematics MAT11
GEO108 Geography GEO18
HIS209 History HIS19
LIT302 Literature LIT22
Populated DEPARTMENT TABLE Data
• BIO14
DepartmentCodes DepartmentNames HODNames Gender
COM10 Computer Science Yusuf Morai Male
ENG12 English Language Bello Morai Male
Biology Aminat Morai Female
CHM15 Chemistry Ibrahim Morai Male
PHY16 Physics Khadijah Morai Female
MAT11 Mathematics Fatima Morai Female
GEO18 Geography Abubakar Morai Male
Operational Steps for Students
Step 1: Creating the Tables and Fields
1. Create a blank database named School_Database.
Belfast International School • ICT Practical Manual Page 4 of 7
2. Go to Create > Table Design. Enter fields for the first table: SubjectCodes (Short Text), SubjectNames
(Short Text), and DepartmentCodes (Short Text).
3. Right-click SubjectCodes and select Primary Key. Save the table as SUBJECT_TABLE.
4. Switch to Datasheet view and fill in the 9 records using the populated data table shown above.
5. Go back to Create > Table Design for the second table. Enter fields: DepartmentCodes (Short Text),
DepartmentNames (Short Text), HODNames (Short Text), and Gender (Short Text).
6. Right-click DepartmentCodes and set it as the Primary Key. Save it as DEPARTMENT_TABLE.
7. Switch to Datasheet view and carefully input the records matching the gender fields exactly as requested
in the question layout.
Step 2: Linking Tables (Relationship Setup)
1. Close both tables. Go to the Database Tools tab on the ribbon and click Relationships.
2. Add both SUBJECT_TABLE and DEPARTMENT_TABLE from the popup window, then close the box.
3. Click and hold DepartmentCodes in the DEPARTMENT_TABLE box, drag it, and drop it directly onto
DepartmentCodes inside the SUBJECT_TABLE box.
4. In the Edit Relationships window, check the box for Enforce Referential Integrity, and click Create.
Step 3: Creating the Queries
Query (a) Steps - Female HODs Filter:
1. Click Create > Query Design. Add the DEPARTMENT_TABLE and close the window.
2. Double-click the fields DepartmentNames, HODNames, and Gender to place them down into the grid.
3. In the Criteria row directly under the Gender column box, type "Female".
4. Click the red Run button. Save this query as Female_HODs. Go to File > Print to print results.
Query (b) Steps - MAT11 Department Extraction:
1. Click Create > Query Design. Add BOTH tables (SUBJECT_TABLE and DEPARTMENT_TABLE) so they
display linked paths, then close.
2. From the boxes, double-click to add fields down below in this exact order: DepartmentNames,
SubjectCodes, SubjectNames, and DepartmentCodes.
3. In the Criteria row directly under the DepartmentCodes column box, type "MAT11".
4. Uncheck the "Show" box under the DepartmentCodes criteria row if you only want the three specific
columns to display in your final table view.
5. Click Run. Save this query as MAT11_Department_Details and select Print.
Answers to Part B Theory Questions
i. Two Precautions Taken:
1. Checked and maintained strict data integrity guidelines to ensure field types matched before trying to
Belfast International School • ICT Practical Manual Page 5 of 7
build primary and foreign key relationships.
2. Saved work continuously (Ctrl + S) to prevent data loss due to unexpected power cuts or computer
system errors.
ii. Definitions:
(a) Primary key: A unique field or combination of fields in a database table that uniquely identifies each
individual record. It completely prevents duplicate rows from being saved.
(b) Table: A structural collection of related data elements arranged cleanly in standard horizontal rows
(records) and vertical columns (fields).
iii. Four Examples of DBMS Packages:
1. Microsoft Access 2. MySQL 3. Oracle Database 4. Microsoft SQL Server
iv. What is a Database?
A database is an organized, structured collection of systematic data stored electronically in a computer,
enabling fast access, search, recovery, and management operations.
4. FRESH PRACTICE QUESTION FOR STUDENTS
PRACTICE EXERCISE (QUESTION 2)
Belfast International School wants to organize its library system records. You are required to create a
clean relational library setup using Microsoft Access.
1. Create a table named BOOKS_TABLE with the fields: BookID (Primary Key), BookTitle, and
AuthorName. Input these records:
• BK201 | Fundamentals of ICT | A.Y. Morai
• BK202 | Core Mathematics Guide | J. Smith
• BK203 | Modern Literature Insights | A. Ibrahim
2. Create another table named BORROWERS_TABLE with fields: BorrowerID (Primary Key),
StudentName, BookID (Foreign Key), and Status. Input these records:
• ST881 | John Musa | BK201 | Returned
• ST882 | Maryam Aliyu | BK202 | Pending
• ST883 | David Luka | BK201 | Pending
3. Create a relationship link between the tables using the shared BookID field.
4. Formulate a query that extracts the StudentName, BookTitle, and Status for all books whose
Status is currently listed as "Pending". Save it as Pending_Loans.
Belfast International School • ICT Practical Manual Page 6 of 7
Solution Blueprint for Practice Exercise
Step 1: Structural Table Creation
• Open Access, design BOOKS_TABLE by assigning BookID as Short Text and making it the Primary Key.
Type the titles and authors as structured.
• Design BORROWERS_TABLE, setting BorrowerID as Short Text and setting it as the Primary Key. Set
BookID data type identically as Short Text so fields can cross-reference perfectly. Fill in student rows.
Step 2: Linking Layout Steps
• Close all active data entries. Navigate to Database Tools > Relationships.
• Bring in both new tables. Click on BookID inside BOOKS_TABLE, drag and connect it down to BookID in
BORROWERS_TABLE. Mark Referential Integrity, and click create.
Step 3: Pending Query Design Steps
1. Navigate to Create > Query Design. Select and add both tables to the design canvas layout.
2. Double-click StudentName and Status from the Borrowers table, and double-click BookTitle from the
Books table.
3. Under the Status column box grid, go to the Criteria line and type "Pending".
4. Click Run. The screen will immediately display a table with Maryam Aliyu and David Luka showing their
pending books. Save the query file as Pending_Loans.
Belfast International School • ICT Practical Manual Page 7 of 7