Lab 10 – To work with the MS Access environment – II SSUET/QR/114
LAB 10
10.1. OBJECTIVE
To work with the MS Access environment – II
10.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
application. 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.
10.2.1. Sorting Records
Sorting and filtering are two tools that allows users to customize how to organize and view the
data, making it more convenient to work with. Sorting records means putting them into a logical
order, with similar data grouped together. This often makes it simpler to read and understand
than unsorted data. By default, Access sorts records by their ID numbers. However, there are
many other ways records can be sorted such as order date for orders, customer names,
categories, prices, etc.
Sorting of text and numbers can be done in ascending (A-Z) or descending (Z-A) order. To sort
records, select the field to sort the data by, then select the order from the Sort & Filter group
on the Home tab on the ribbon. The table will be sorted by the selected field.
10.2.2. Filtering Records
Filtering allows the designer to view only the
data they want to see. When the user creates a
filter, they set criteria for the data to be displayed.
The filter then searches all the records in the
table, finds the ones that meet the search criteria,
and temporarily hides the ones that do not.
Filters are useful because they allow designers to
focus in on specific records without being
distracted by the data, they are uninterested in.
For instance, in a database containing customer
and order information, the user can create a filter
to display only customers living within a certain
city or only orders containing a certain product.
Viewing this data with a filter would be far more
convenient than searching for it in a large table.
CET-103L Computer Fundamentals 56
Lab 10 – To work with the MS Access environment – II SSUET/QR/114
To create a filter, select a field then click on the drop-down arrow next to it. A drop-down menu
with a checklist will appear. Only checked items will be included in the filtered results.
Clicking Select All will select or deselect everything at once. Click OK to apply the filter.
A filter can be quickly toggled from the Sort & Filter group on the Home tab on the Ribbon.
Toggling the filter allows the designer to turn it on and off. To view the records without the
filter, click the Toggle Filter command. To restore the filter, click it again.
Filtering by Selection
Filtering by selection allows the user to select specific data from the table and find data that is
similar or dissimilar to it. Creating a filter with a selection can be more convenient than setting
up a simple filter if the field to be filtered contains many items. For example, in a bakery’s
database, if the user wants to search records containing the word chocolate, that word can be
selected and then a filtered created based on the selection.
Filtering by selection can be done based on the following options:
• Contains – Includes only records with cells that contain the selected data.
• Does Not Contain – Includes all records except for those with cells that contain the
selected data.
• Ends With – Includes only records whose data for the selected field ends with the
search term.
• Does Not End With – Includes all records except for those whose data for the selected
field ends with the search term.
To create a filter from a selection, select the cell or data to create the filter with. If the filter is
to be created based on a word in the cell, select the appropriate word. From the Sort & Filter
group on the Home tab on the Ribbon, click the Selection drop-down arrow. Select the type of
filter to apply. The filter will be applied.
Filtering by a Search Term
Filters can also be created by entering a search term and specifying the way Access should
match data to that term. Creating a filter from a search term is similar to creating a filter from
a selection. Some of the options to match the search terms are same as those of filtering by
selection. Additionally, more options can be selected when filtering based on a search term.
• Equals – Includes only records with data that is identical to the selected data.
• Does Not Equal – Includes all records except for the data that is identical to the
selection.
CET-103L Computer Fundamentals 57
Lab 10 – To work with the MS Access environment – II SSUET/QR/114
• Begins With – Includes only records whose data for the selected field begins with the
search term.
• Does Not Begin With – Includes all records except for those whose data for the selected
field begins with the search term.
• Greater Than – Includes only records with numbers in that field that are greater than
or equal to the number entered.
• Less Than – Includes only records with numbers in that field that are less than or equal
to the number entered.
• Between – Includes records with numbers that fall within a certain range.
To filter by a search term, select the drop-down arrow next to the specific field to filter. Hover
the mouse over Text Filters to display the list of options. Select the option to display the Custom
Filter dialog box and type the search term. Click OK to apply the filter.
10.2.3. Input Mask
An input mask restricts how data is entered into the database. It is a set of simple rules that
specify the format in which data can be entered into a field. For example, an input mask of
(999) 00000000 can be used to determine how (landline) phone numbers are entered. In this
case, the first three digits (999) represent the area code, while the rest of the digits represent
the phone number. With any input mask, 9 specifies an optional number while 0 specifies a
mandatory number.
Input masks can be applied to fields in the Design View. Select the field to apply the mask.
Then from the field properties, click on Input Mask row. Click on the three dots to launch the
Mask Wizard. Then follow the options until the mask is applied.
CET-103L Computer Fundamentals 58
Lab 10 – To work with the MS Access environment – II SSUET/QR/114
10.2.4. Lookup Table
A lookup table is a table that contains data that is
referenced by another table. The other table will have a
lookup field that can "lookup" the data in the lookup table.
In Access, the lookup field displays the data as a drop-
down list (or combo box) so that the user can select the
desired value from the list. The values of the lookup field
come directly from the lookup table.
To create a lookup table, switch to the Design View of the
database and click on the Data Type cell of field to create
the lookup table for. This will launch the Lookup Wizard.
Follow the steps of the Wizard similar to how relationships are created. Ensure to Enable Data
Integrity. When prompted, save the table. The lookup table, corresponding lookup field, and
appropriate relationships will be created.
10.2.5. Queries
The real power of a relational database lies in its ability to quickly retrieve and analyze the data
by running a query. Queries allow the user to pull information from one or more tables based
on a set of defined search conditions. The ability to create complex queries across multiple
tables with various data types is one of the features that distinguishes a database management
system from a simple spreadsheet application.
Queries are a way of searching for and compiling data from one or more tables. Running a
query is like asking a detailed question of the database. When a user builds a query in Access,
they are defining specific search conditions to find exactly the data required. A well-designed
query can give information the user might not be able to find out just by examining the data in
the tables.
Queries are far more powerful than the simple searches or filters used to find data within a
table. This is because queries can draw their information from multiple tables. For example,
suppose a user wants to view both customers and orders at once. Individually they can be done
using sorting and filtering. However, to do so simultaneously a query would be required. The
user can easily run a query to find the name and phone number of every customer who has
made a purchase within the past week.
Access has a Query Wizard that allows users to choose the type of query to run, then walks
them through the creation of the query. Access also allows users to go straight to the Query
Design view which gives the user more control over creating the query. For more advanced
users, Access provides a SQL View that allows them to construct queries using SQL code.
Create a Single-table Query
From the Create tab on the Ribbon, select Query Design command in the Queries group. This
will open the Query Design view.
CET-103L Computer Fundamentals 59
Lab 10 – To work with the MS Access environment – II SSUET/QR/114
Select the table to use in the query, then click Add. The tables will be visible in the Query
Design view. In the table window, double-click the field names to include in the query. They
will be added to the design grid in the bottom part of the screen.
Set the search criteria by clicking the cell in the Criteria: row of each field to filter. Typing
criteria into more than one field in the Criteria: row will set the query to include only results
that meet all criteria. To set multiple criteria such that records not meeting all criteria are also
displayed, type the first criteria in the Criteria: row and additional criteria in the or: row and
the rows beneath it.
Run the query by clicking the Run command on the Design tab.
CET-103L Computer Fundamentals 60
Lab 10 – To work with the MS Access environment – II SSUET/QR/114
The query results will be displayed in the query's Datasheet
view, which looks like a table. Save the query by clicking the
Save command in the Quick Access Toolbar. When prompted
to name it, type the desired name, then click OK.
Create a Multi-table Query
A multi-table query uses multiple connected
tables to answer more complex questions. They
require careful planning and exact knowledge
of what information is required. For example,
suppose that in the bakery’s database, the user
wants to know which customers are not from
the city but from nearby cities and have
previously ordered from the bakery. Formally,
the user wants to answer the following
question: Which customers live in our area, are
outside the city limits, and have placed an order
at our bakery?
To answer the question, the user will need the
following information: Customer names,
addresses, phone numbers, email addresses,
and order ID numbers. The first four fields can
be obtained from the Customer table but the last
field belongs in the Orders table. Hence, the
user required information from two tables based on the criteria outlined in the question.
To create a multi-table query, add two or more tables when creating the query in the Query
Design view. The tables will appear in the Object Relationship pane, linked by a join line
showing its relationships. This relationship can be changed by double-clicking the join line.
Next, in the table windows double-click the field names to include in the query. The fields can
be selected from any table, and they will be added to the design grid in the bottom part of the
screen.
CET-103L Computer Fundamentals 61
Lab 10 – To work with the MS Access environment – II SSUET/QR/114
Set field criteria by entering the desired criteria in the criteria row of each field. Then Run the
query and save it if required.
Create a Parameter Query
A parameter query allows the user to create a query that can be updated easily to reflect a new
criterion, or search term. When the user runs a parameter query, Access will prompt the user
for a search term and then show the query results that reflect that search.
Parameter queries are created just like regular queries. The only difference is that the prompt
for the parameter is placed in the Criteria: row. In the Criteria: row, type the phrase to appear
in the prompt that will pop up each time query is run. Ensure that the prompt is enclosed in
brackets [ ].
10.2.6. Reports
Reports allow users to organize and present data in a reader-friendly, visually appealing format.
They give users the ability to present components of the database in an easy-to-read, printable
format. Access makes it easy to create and customize a report using data from any query or
table in the database.
To create a report, open the table or query to use in the report. On the Create tab on the Ribbon,
select the Report command from the Reports group.
CET-103L Computer Fundamentals 62
Lab 10 – To work with the MS Access environment – II SSUET/QR/114
A new report will be created based on the selection. The report displays the fields in columns,
with the field names as column headers. It has basic formatting applied, and this can easily be
changed using the various formatting options. The fields in the report can be resized by clicking
and dragging the edge of the fields. They can also be deleted by selecting the field and pressing
the Delete key on the keyboard. Further modifications to the report can be made in the Design
view of the report.
Reports are shown in the Backstage view by default. Print Preview can also be used to view
what the printed report will look like. It also allows the user to modify the way the report is
displayed, print it, and even save it as a different file type. Page layouts, margins, page
orientation, etc. can be changed from the Print Preview. Reports can also be exported to
different file formats such as an Excel file, a text file, PDF, etc.
Reports can also be created using the Report Wizard. The Report Wizard makes it easy to create
reports using fields from multiple tables and queries. It also lets the users choose how the data
will be organized.
To create a report using the Report Wizard, select the Report Wizard command on the Create
tab on the Ribbon. Then follow the prompts until the report is created.
CET-103L Computer Fundamentals 63
Lab 10 – To work with the MS Access environment – II SSUET/QR/114
10.3. EXERCISES
10.3.1. Task 01
Perform the following operations on the database created in Lab 9. Then attach screenshots of
your results.
1. Apply an input mask to the Batch field in the Students table.
2. Connect the Students table and the Courses table using a lookup table. Then replace the
CourseID field in the Students table with the CourseName field.
3. Create and save a query that shows the StudentName, Batch, and Section of all students.
4. Create a query that shows the StudentName, Batch, and Section of all students enrolled in
only one subject.
5. Modify the query made in (4) to take the subject name as input from the user.
6. Create a report based on the query created in (5). Format the report so that it is readable
and appears on only one page.
CET-103L Computer Fundamentals 64