Lab 9 – To work with the MS Access environment – I SSUET/QR/114

LAB 9
9.1. OBJECTIVE
To work with the MS Access environment – I

9.2. THEORY
Microsoft Access is a database creation and management software offered by Microsoft. It uses
the Microsoft Jet Database Engine and comes as a part of the Microsoft Office suite of
applications. Microsoft Access offers the functionality of a database and the programming
capabilities to create easy to navigate screens (forms). It helps users analyze large amounts of
information and manage data efficiently.

9.2.1. Database
A database is a collection of data that is stored
in a computer system. Databases allow their
users to enter, access, and analyze their data
quickly and easily. Think of it as a collection
of lists. For example, the database of patient
information at a doctor's office will contain a
list of past appointments, a list with medical
history for each patient, a list of contact
information, and so on. The more lists there are, the more complex the database will be.
Unlike Excel, Access is far stronger at handling non-numerical data, like names and
descriptions. Non-numerical data plays a significant role in almost any database, and it is
important to be able to sort and analyze it.
The file which stores the entire database is called the database file. It is saved on a hard drive
or other such storage devices.

9.2.2. Relational Database

Databases provide connectivity. Access works with relational
databases. A relational database is able to understand how
lists and the objects within them relate to one another. For
example, consider the lists shown in the image. The People
list and the Cookies list which store information about people
and cookies, respectively, are already available. The third list
is created using the already available list and relates which
batch of cookies was made for whom. The database can relate
that the details of the information in the Batches list can be
obtained from the other two lists. An Excel workbook lacks this capability.
Simply put, relational databases can recognize what a human can: If the same words appear in
multiple lists, they refer to the same thing. Excel would treat all of these things as distinct and
unrelated pieces of information. The fact that relational databases can handle information this
way allows users to enter, search for, and analyze data in more than one table at a time,
simplifying complicated tasks and making them fairly user-friendly.

CET-103L Computer Fundamentals 46

Lab 9 – To work with the MS Access environment – I SSUET/QR/114

9.2.3. Tables
A table is an object which uses vertical rows and horizontal columns to store data. Think of
tables in Excel; Access tables are similar. Access stores its lists of data in tables, which allow
users to store even more detailed information. Tables are good for storing closely related
information. The following image shows a sample table storing People information.

In Access, rows are called records and columns are called fields.
A field is a way of organizing information by the type of data it is. Every piece of information
within a field is of the same type. For example, every entry in a field called Name will be a
name; every entry in a field called Cell Phone will be a phone number.
A record is a unit of information. Every cell in a given row is part of that row’s record. Each
record can span several fields. Even though the information in each record is organized into
fields, it belongs with the other information in that record. Each record has a unique ID number
that identifies it. The ID number for a record refers to every piece of information contained on
that row.
For example, the table below stores details about the customers of a shop in a single table. Each
customer is represented by a unique record, and each type of information about these customers
is stored in its own field. Adding more information about a customer simply requires creating
a new field within the same table.

9.2.4. Access Interface

Access uses the same Ribbon interface used by the other software packages offered by the
Microsoft Office Suite. The Ribbon contains multiple options tailored to Access use as well as
other common options shared among the MS Office software packages such as the Clipboard,
Sort & Filter, etc.

CET-103L Computer Fundamentals 47

Lab 9 – To work with the MS Access environment – I SSUET/QR/114

9.2.5. Table Views

The table created is in Datasheet View. This view displays the table as a grid. The fields are
displayed as columns, and the records are displayed as rows. The field names are listed as the
column headers and each row represents a record.
The Datasheet View displays the data in the cells of the table. Moreover, the datatype of a field
can be found by selecting the field name and then selecting the Fields tab on the Ribbon. The
field's data type and other properties will be listed on the right side of the Ribbon.
The other view is the Design View. This view does not display any data but rather displays
other settings. The fields are listed vertically, and the datatype is listed next to the field name.
Clicking on a field displays the properties for that field which can be changed as required.

CET-103L Computer Fundamentals 48

Lab 9 – To work with the MS Access environment – I SSUET/QR/114

9.2.6. Navigation Pane

The Navigation pane is a list containing every
object in your database. For easier viewing, the
objects are organized into groups by type. Users
can open, rename, and delete objects using the
Navigation pane.
By default, objects are sorted by type, with tables
in one group, forms in another, and so on. However,
based on their preferences, users can sort the
objects in the Navigation pane into groups of their
choosing. There are four sort options:
• Custom – Allows users to create a custom
group for sorting objects. After applying the
sort, the desired objects are simply dragged
to the new group.
• Object Type – Groups objects by type.
This is the default setting.
• Tables and Related Views – Groups
forms, queries, and reports with the tables
they refer to.
• Created Date or Modified Date – Sorts objects based on when they were created or
last edited.
The Navigation Pane can be used to access objects i.e., tables, forms, etc. The object will appear
as a tab in the Document Tabs bar. By default, the most recently opened object will appear in
the main window as the current object. To view another open object, click its tab in the
Document Tabs bar.

9.2.7. Creating a Database

To create a new database, open Access and select Blank Database. Give it a name, select where
the database should be stored, and click
Create. A blank database will be
created which contains one table ready
to be configured.
An alternative method is to select the
Blank Database option from the File
menu.

CET-103L Computer Fundamentals 49

Lab 9 – To work with the MS Access environment – I SSUET/QR/114

9.2.8. Adding Records and Fields

To add a new field a table, click the Click to Add option at the top of a column. This will open
a menu that provides a list of different datatypes. To select the appropriate datatype, think of
the type of data that the field will contain. For example, a field for storing a person's first name
will probably use a Short Text data type, whereas a field for storing their date of birth will
probably use Date & Time. Datatypes can always be changed later.
After selecting the datatype, Access highlights the column header so that the user can name the
field. Simply type in the column to give a name to the field. Repeat these steps to add as many
fields as desired.
Access automatically creates an ID field with the table. This field has the datatype AutoNumber
and contains the unique ID of the record and cannot be modified. It can, however, be renamed.
To rename any field, right-click in the field and select Rename Field. Access highlights the
field name for the user to rename it. A new name can now be entered as required.

There are three ways to add new records to a table. First, is to add records via the Records
group in the Home tab. Clicking on the New command automatically shifts the focus a blank
row in the table. This is usually the last row of the table.

Alternatively select the new (blank) record command on the Record Navigation bar at the
bottom of the screen. Access automatically shifts the cursor to the last blank row of the table.

CET-103L Computer Fundamentals 50

Lab 9 – To work with the MS Access environment – I SSUET/QR/114

The last way is to simply begin typing on the last blank row of the table.
Access is designed to save records automatically. After entering a record, the user can either
select a different record or close the object, and Access will save the record. However, in certain
situations users many want to save a record manually.
To save a record, Select the Home tab and locate the Records group. Click the Save command.
The record will be saved.

To delete a record, select the entire record by clicking the gray border on
the left side of the record. Click the Delete command in the Records group
in the Home tab. A dialog box will appear. Click Yes. The record will be
permanently deleted.
The ID numbers assigned to records stay the same even after you delete a
record. For example, if the 205th record in the table is deleted, the sequence
of record ID numbers will read … 204, 206. 207 …

9.2.9. Saving Objects

All objects created need to be saved before closing the database. The user will also be prompted
to save any unsaved work when they attempt to close the database.
To save an object, right-click on the object’s tab and select Save. A dialog box will appear
prompting the user to name the object. Give an appropriate name and click OK.
The object will appear in the in the left Navigation Pane.
Objects can also be saved by clicking on the Save command in the Quick Access Toolbar.

9.2.10. Closing Objects

To close an object, select it and click the X to the right of the Document Tabs bar. If there are
any unsaved changes to the object, the user will be prompted to save it. Select Yes to save, No
to close it without saving the changes, and Cancel to leave the object open.

CET-103L Computer Fundamentals 51

Lab 9 – To work with the MS Access environment – I SSUET/QR/114

9.2.11. Renaming Objects

To rename an object, first close it. In the Navigation pane, right-click the desired object, then
select Rename. Type the new object name, then press Enter on the keyboard.

9.2.12. Relationships
In relational database terms, a relationship is a situation where multiple tables can contain
related data that is linked by a common field. A relationship consists of a parent table and a
child table. The child table references the parent table by having a field that matches a field in
the parent table. The child's field is referred to as a foreign key. The parent's field is the primary
key.
In a relationship, any data entered into the child's foreign key field must match a value from
the parent's primary key field. By ensuring that the foreign key's data matches data in the
primary key, users can ensure that all records in the child table will have an associated record
in the parent table.
A type of relationship is a one-to-many relationship. If two tables such as Courses and Students
have a one-to-many relationship, it means that one course can have many students but one
student cannot have many courses. This is only one of the three types of relationship supported
by Access.
To create a one-to-many relationship, Click on Relationships from the Database Tools tab on
the Ribbon. The Show Tables dialog box should appear. Select the tables from the list and click
Add. A relationship will be formed between these tables. Click Close to close the dialog box.
Then click and drag the common field from one table over to the same field in the other table
and release. In the image, Albums.ArtistId field is dragged over to the Artists.ArtistId field.

The Edit Relationships dialog box appears. Check Enforce Referential Integrity and click
Create. The Enforce Referential Integrity option will ensure that child records cannot reference
a non-existent parent. So if a user tries to enter an album with an ArtistId that is not in the
Artists table, Access will prevent them from doing so. Access will also prevent the user from
deleting an artist that has albums attached. Selecting the Cascade Update Related Fields and
Cascade Delete Related Fields options will delete (or update) all related records whenever a
primary record is deleted/updated.
Once the relationship is created, a diagram will appear, representing the relationship. Save the
relationship to use it in the database.

CET-103L Computer Fundamentals 52

Lab 9 – To work with the MS Access environment – I SSUET/QR/114

9.2.13. Forms
Data can also inserted in to the database using forms. Forms ensure that the user is entering the
right data in the right location and format. This can help keep the database accurate and
consistent.
Forms are a way of requiring information in a specific format, which means the person filling
out the form knows exactly which information to include and where to put it. This is also the
case for forms in Access. When the user enters information into a form in Access, the data goes
exactly where it is supposed to go: into one or more related tables. It can also make the data
entry process easy and user-friendly.
To create a form, first select the table from the Navigation Pane to use for the form, and click
Form on the Create tab on the Ribbon.

Access immediately creates a form based on the selected table. The form displays all fields as
label/field pairs. The label allows users to see which field is which. The field is presented in an
editable form element such as a text box, combo box, etc. The user can then edit the underlying
data using the form elements. All fields in the table are automatically included in the form.
The form is created in Layout View. The Layout View provides a more visual layout for editing
the form. It bears a close resemblance to the actual form that the user will see. It allows the
user to make layout changes to the form affecting the way the form appears to users. Layout
View allows users to edit the form properties while it has real data.
To make changes to the appearance of the form, select Design View from the View Button on
the Ribbon. Design View provides a more detailed view of the form's structure than Layout
View. Design View also allows users to modify the form without having any data getting in
the way. Most tasks can be done in either Design View or Layout View, but there are some
form properties that can only be changed in Design View.
When using the Form command on an existing table, all of the fields from the table are included
in the form. However, if later additional fields are added to the table, these fields will not
automatically show up in existing forms. In situations like this, users can add additional fields
to a form. To add a field to a form, select the Design tab, then locate the Tools group on the
right side of the Ribbon. Click the Add Existing Fields command. The Field List pane will
appear. Double-click the desired field(s). The field will be added.

CET-103L Computer Fundamentals 53

Lab 9 – To work with the MS Access environment – I SSUET/QR/114

This procedure can also be used to add field to a blank form.

Forms can be modified by adding buttons, formatting, or other navigation options to make them
user-friendly. An important data entry feature in forms is a combo box that allows users to
select an option from a given list. This list can be populated from another table, allowing users
to access data from multiple tables into a single form. To add a combo box to a form, open the
form in Layout View. Click the Combo Box icon from the Design tab on the Ribbon. Now
click the form in the location that you want the combo box to be inserted. The Combo Box
Wizard will open. Follow the Wizard to set up the combo box. It will appear on the form.
Once the form is created, it must be saved using the Save command on the Quick Access
Toolbar. When prompted, type a name for the form, then click OK.

9.2.14. Using Forms

To add a new record using a form, new (blank) record command on the Record Navigation
bar at the bottom of the screen. A form with blank field values will be opened. Enter the
relevant data in the appropriate textbox and navigate through the form using the Tab key on
the keyboard. Once the Tab key is pressed in the last field, it will save the last record being
edited to the table and move to the next empty record.
In addition to adding new data to a table, an Access input form lets users modify data that is
already in a table as well. To modify a record, access the record by using the < or > commands
or using the Search field on the Record Navigation bar. In the field values, make the appropriate
change and select Save in the Records group in the Home menu. Alternatively, press the Tab
key in the last form field. The changes will be saved.

CET-103L Computer Fundamentals 54

Lab 9 – To work with the MS Access environment – I SSUET/QR/114

9.3. EXERCISES
9.3.1. Task 01
Perform the following operations then attach screenshots of your results.
1. Create a database named StudentInformation.
2. Create a table Students. It should have the fields: StudentID, StudentName, Batch, Section,
CourseID. Choose appropriate datatypes for the fields.
3. Create another table Courses having the fields: CourseID, CourseCode, CourseName.
Choose appropriate datatypes for the fields.
4. Make an appropriate relationship between the two tables.
5. Create a form to enter data into the Courses table. Format the table to make it easy to use.
Add 10 records.
6. Create another form to enter data into the Students table. Your table should have a combo
box for courses. Format the table to make it easy to use. Add 10 records.

CET-103L Computer Fundamentals 55