Notesale: Turn your study into money

Document Preview

Extracts from the notes are below, to see the PDF you'll receive please use the links above

---

GE-161L

Laboratory 09
Introduction to Microsoft ® Access - I

Version: 1
...
0

Release Date: 12-03-2022

Department of Information Technology
University of the Punjab

GE-161L Introduction to ICT

FALL 2021

Contents:











Learning Objectives
Required Resources
General Instructions
Background and Overview
o What is a Database?
o Microsoft ® Access
Activities
o Pre-Lab Activity
 Open Microsoft ® Access
 Using Navigation Pane
 Change Views
 Creating Database
 Creating Table
 Record Navigation
 Task 01: Adding Tables to Database
o In-Lab Activity
 Add Record
 Delete Record
 Primary Key
 Find & Replace data
 Sort Data
 Filter Data
 Tables Relationship
 Input Masks
 Create Forms
 Apply theme
 Create Reports
 Apply theme
 Task 01: Teacher-Student Relation
 Task 02: Creating Forms & Reports
o Post-Lab Activity
 Task 01: Customers Record
Submissions
Evaluations Metric
References and Additional Material
Lab Time and Activity Simulation Log

Laboratory 09 – Microsoft ® Access - I

Page 2 of 27

GE-161L Introduction to ICT

FALL 2021

Learning Objectives:






Familiarization with Microsoft ® Access Environment
Creating Database in Microsoft ® Access
Creating Tables in Microsoft ® Access
Creating Forms in Microsoft ® Access
Creating Reports in Microsoft ® Access

Resources Required:



Computer / Laptop
Microsoft ® Access

General Instructions:



This is an individual lab, you are NOT allowed to discuss your solution with your colleagues,
not even allowed to ask how is he/she doing, this may result into negative marking
...

Your TAs will be available in the lab for your help
...


Teachers:

Course Instructor

Prof
...
Syed Waqar ul Qounain
Usman Ali

Teacher Assistants

Saad Rahman
Mahreen Asama

Laboratory 09 – Microsoft ® Access - I

Page 3 of 27

GE-161L Introduction to ICT

FALL 2021

Background and Overview:
What Is a Database?
People often need to retrieve specific data rapidly while on the job
...
The registrar at a university may have to look up a student’s grade point average or rapidly
determine if the student has any outstanding fees before processing his or her class registration
...
The type of software used for such tasks is a database management system
...
The most common type of database used with personal
computers today is a relational database
...

A database is a collection of related data that is stored on a computer and organized in a manner that
enables information to be retrieved as needed
...
Typically data in a database is organized into fields, records, and
files
...
A record (today more commonly called a row) is a
collection of related fields, for example, the ID number, name, and address
...

Microsoft ® Access stores data in its own format based on the Access Database Engine (formerly Jet
Database Engine)
...


Laboratory 09 – Microsoft ® Access - I

Page 4 of 27

GE-161L Introduction to ICT

FALL 2021

Activities:
Pre-Lab Activities:
Open Microsoft ® Access:


Type “Access” in the search bar

Fig
...
2 (Open Blank Database)
Using Navigation Pane:

Fig
...
The table is displayed in Design View

Fig
...

Creating a Database using a Template:


On the top of the startup screen window, in the “Search for online templates” search box,
type “personal”, and then press “Enter

Fig
...
6 (Database Templates)


Click the “Create button” at the bottom of the preview pane

Fig
...
8 (Blank Database)




In the “File Name” box, type “Database”
If you want to save the file in a location other than the one shown beneath the File Name box,
click the “folder icon” to the right of the File Name box and browse to a different location
Click “Create button” to create the blank database
...
9 (Blank Database)
Creating a Table:



On the “Create” tab in the “Templates” group, click the “Application Parts” button to
display the gallery
In the “Quick Start” section of the gallery, click “Comments”

Laboratory 09 – Microsoft ® Access - I

Page 9 of 27

GE-161L Introduction to ICT

FALL 2021

Fig
...
11 (Creating Table)



In the “Create Relationship” dialog box that appears, select the “There is no relationship”
option button
Click “Create”

Laboratory 09 – Microsoft ® Access - I

Page 10 of 27

GE-161L Introduction to ICT

FALL 2021

Fig
...


Fig
...
14 (Navigation buttons)







Click the “First record” button
...
The selection moves to the next record
Click the “Last record” button
...
The selection moves to the previous record
Click the “Search box” to position the insertion point
“Current record box” shows the position of current record

Task 01: Adding Tables to Database

[Estimated 15 minutes / 15 marks]

You are a student and you want to keep track of your grades
...
The
subject of your email should be “Your RollNo_Pre-Lab09”
...
Notice the “asterisk” in the Record Selector box, which indicates that this is a new record

Fig
...
Notice that the asterisk has changed to a “pencil icon”
Type “Saad” and press “Tab”

Similarly Add Record in the second row
...
16 (Add Record)
Delete Record:


Click the “Record Selector box” to the left of the “ID” field of the first record “0”

Fig
...
18 (Delete Record)
Define a Primary Key:
A primary key is a column that uniquely identifies a record or row in a table
...
Each table should have a primary key, and
some tables might have two or more
...




On the “Home” tab, in the “Views” group, click the bottom half of the “View” button
Select “Design View”

Fig
...
20 (Row Selector)


On the “Design” tab, in the “Tools” group, click the “Primary Key” button
...
A key icon appears on the “ID” row to designate the field as a
primary key
...

Find & Replace Data:


On the “Home” tab, in the “Find” group, click the “Find” button
...
21 (Find & Replace)






Click the “Replace” tab in the “Find and Replace” dialog box
Type “Ahmad” into “Find What” box
Type “Ali” into “Replace With” box
Click the down arrow beside the “Look In” menu, and then select “Current document” if it
is not already selected
Click the down arrow beside the “Match” menu and then select “Any Part of Field” if it
already is not selected

Fig
...
23 (Find & Replace)
Sort Data within a Table :




Click the header row of the “ID” field to select it
On the “Home” tab, in the “Sort & Filter” group, click the “Descending” button
...
24 (Sort data)
Filter Data:




Select the “Name” field
“Right click” and select “text filters” from shortcut menu
Click “Begins With…”

Fig
...




Enter “S” in “Name begins with” field
Click “OK”

Fig
...


Fig
...
In a one-to-one relationship, both tables have a common field with the same data
...
In a many-to-many relationship, many records in the first table can have many records in the
second table
...
28 (Table Relationships)
“Show Table” dialog appears
...
29 (Show Table dialog)




Click the “ID” primary key field in the “Student” table, drag it to the “ID” field of the
“Contacts” table, and then release the mouse button
...
30 (Edit Relationships dialog)


Click “Create”

Laboratory 09 – Microsoft ® Access - I

Page 18 of 27

GE-161L Introduction to ICT

FALL 2021

A relationship line representing the one-to-one table relationship of the Student and the Contacts tables
is displayed

Fig
...
An input mask can require
users to enter dates in a specific format, for example, DD-MM-YYYY, or telephone numbers that
follow the conventions for a specific country
...
”) on the far right of the cell

Fig
...


Fig
...
34 (Input Mask)
Create a Simple Form:



Open the “Contacts” table
On the “Create” tab, in the “Forms” group, click the “Form” button
...
35 (Creating Form)



...
36 (Form themes)


On the “Form Layout Tools Design” contextual tab, in the “Themes” group, click the
“Themes” button

Laboratory 09 – Microsoft ® Access - I

Page 21 of 27

GE-161L Introduction to ICT

FALL 2021

Fig
...
38 (Form Themes)
Create Reports:
Reports are commonly used as formatted hard copies of table or query data
...
The purpose of a report is to allow users to view
data, not edit it




...
On the “Create” tab, in the “Reports” group, click the “Report” button

Fig
...


Fig
...
41 (Change View)


On the “Report Layout Design” contextual tab, in the “Themes” group, click the “Themes”
button

Laboratory 09 – Microsoft ® Access - I

Page 23 of 27

GE-161L Introduction to ICT

FALL 2021

Fig
...
43 (Report Themes)

Laboratory 09 – Microsoft ® Access - I

Page 24 of 27

GE-161L Introduction to ICT
Task 01: Teacher Student Relation

FALL 2021
[20 minutes / 20 marks]

Fig
...
45 (In-Lab task)









Create two tables, “Teachers” and “Students” respectively as shown above in the figures
There should be “ten” entries in the “Students” table and three entries in the “Teachers” table
Make “Student_ID” primary key in the “Students” table
Make “Teacher_ID” primary key in the “Teachers” table
Add a relationship between tables on the basis of “Teacher_ID”
Name access file with “Your Roll No”
Email the file to the TA, the subject should be “Lab 09_Task 01_Your Roll No”

Task 02: Formatting & Creating Forms and Reports






[20 minutes / 30 marks]

For the Tables in the above task, create Forms & Reports respectively
The theme for Reports & Forms should be “Organic”
Filter “Name” columns of tables on the basis on names which “contains A”
Name access file with “Your Roll No”
Email the file to the TA, the subject should be “Lab 09_Task 02_Your Roll No”

Laboratory 09 – Microsoft ® Access - I

Page 25 of 27

GE-161L Introduction to ICT

FALL 2021

Post-Lab activities:
Task 01: Customers Record










[Estimated time 30 minutes / 40 marks]

Create Table:
 Named “Customers”
 You should have at last 10 Customers with their information included in the table
Table Fields:
 ID (Primary Key)
 First Name
 Last Name
 City
 Address
 Date of Purchase
 Amount of Purchase
Sort table by “City” field in “Descending Order”
Apply “Input Mask” on “Date of Purchase” of your own choice
Create “Report” of the “Customers” table
Create “Form” of the “Customers” table
Apply “Wisp” theme on Report and Form

Submissions:




For Pre-Lab Activity: Perform Pre-Lab as mentioned above
...
Then zip the whole folder (RollNo_Pre-Lab-09
...

For In-Lab: Perform mentioned tasks of In-Lab activity
...
Then save each document in folder “RollNo_In-Lab-09”
...
Save the respective document in
folder “RollNo_Post-Lab-09”
...
zip), and email
it to your respective TA
...

Division of Pre-Lab tasks:
 Task 01 (Creating Tables)
Division of In-Lab tasks:
 Task 01 (Teacher-Student Relation)
 Task 02 (Creating Reports and Forms)
Division of Post-Lab tasks:
 Task 01 (Customers Record)

[20 marks]
[20 marks]
[40 marks]
[20 marks]
[20 marks]
[40 marks]
[40 marks]

References and Additional Material:


Mary Lemons, Microsoft Official Academic Course, Microsoft Access 2016, Wiley Publisher,
2016
...

https://drive
...
com/drive/u/1/folders/1V9nh8WIKOIQvi_ig98_YCaP7Vvei-tQz



Learn Microsoft ® Access:
https://support
...
com/en-us/access

Laboratory 09 – Microsoft ® Access - I

Page 26 of 27

GE-161L Introduction to ICT

FALL 2021

Lab Time Activity Simulation Log:













Slot
Slot
Slot
Slot
Slot
Slot
Slot
Slot
Slot
Slot
Slot
Slot

–
–
–
–
–
–
–
–
–
–
–
–

01 –
02 –
03 –
04 –
05 –
06 –
07 –
08 –
09 –
10 –
11 –
12 –

00:00
00:15
00:30
00:45
01:00
01:15
01:30
01:45
02:00
02:15
02:30
02:45

–
–
–
–
–
–
–
–
–
–
–
–

00:15:
00:30:
00:45:
01:00:
01:15:
01:30:
01:45:
02:00:
02:15:
02:30:
02:45:
03:00:

Settlement and attendance
Demonstration on screen (Microsoft ® Access)
Demonstration on screen (Microsoft ® Access)
Demonstration on screen (Microsoft ® Access)
Demonstration on screen (Microsoft ® Access)
Demonstration on screen (Microsoft ® Access)
Demonstration on screen (Microsoft ® Access)
Discussion on In-Lab Task
In-Lab Tasks
In-Lab Tasks
In-Lab Tasks
Discussion on Post-Lab Task

Laboratory 09 – Microsoft ® Access - I

Page 27 of 27



---