COMM 205 Winter 2015 Term 2

Tutorial 2: Microsoft Access Introducing Tables, Relationships, and Creating Queries

The purpose of this tutorial is to enable you to
1) explore the tables and relationships in Access database;
2) create simple queries using Query Wizard and specify query criteria; and
3) create some special-purpose queries including calculated queries and crosstab queries.
You should download the sample database, C205_Access_T2.accdb, from the UBC Connect as the foundation
to create queries in this tutorial.

Understanding Database Fundamentals

A database is a collection of data organized as meaningful information that can be accessed, managed, stored,
queried, sorted, and reported. Data is what is entered into a database while information is the finished product
that is produced by the database.
A database management system (DBMS) is a software system that provides the tools needed to create, maintain,
and use a database. Microsoft Access is an example of DBMS.
The foundation of every relational database is a table where data, such as customers name, products
description, etc. is stored. Tables organize data into columns and rows. Columns display a field, the smallest
data element of a table. Each row in a table contains a record. Each record contains multiple fields.

Exploring Access Views

Access provides two different ways to view a table: the Datasheet view and the Design view.
The Datasheet view is a grid containing fields (columns) and records (rows), similar to an Excel spreadsheet.
You can view, add, edit and delete records in the Datasheet view. The Design view gives you a detailed view of
the tables structure and is used to create and modify a tables design by specifying the fields it will contain, the
fields data types, and their associated properties.
The data types define the type of data that will be stored in a field, such as text, number, date/time, currency, etc.
The field properties define the characteristics of the fields in more details. For example, for the DateJoin field in
the customer table, you could set a field property that requires a Short Date format.

Designer View

COMM 205 Winter 2015 Term 2

Copyright 2016 Y.M.Cheung

Datasheet View

MS Access Tutorial

Page 1

Demonstration:
Switch between the Datasheet view and the Design view of the Customer table.
Key Steps:
(a) Open the Customer table (tblCustomer).

(b) Click the Home tab and click View in the Views group to toggle between the current view and the previous
view.
(c) Use the Navigation bar (on the bottom of each table) to go to Next Record, and then Last Record.
(d) Use the Search box to locate a customer, e.g. Susan. What do you see?
(e) Add a new customer record using any name. Click the record selector
to save the record. Are
there any other ways to save the record?

Understanding Relationships
Microsoft Access has the ability to create relationship between two tables, whereas Excel cannot.
When relational databases are designed properly, users can easily combine data from multiple tables to create
queries, forms and reports.
Relationships are created in the Relationships window after the database tables are created.
The most common method of connecting two tables is to connect the primary key from one table to the foreign
key of another. For example, the MovieNo (primary key) in the Movie table is joined to the MovieNo (foreign
key) in the Video table.

primary
key

foreign
key

One Movie has Many Video (formats)

COMM 205 Winter 2015 Term 2
Copyright 2016 Y.M.Cheung

MS Access Tutorial

Page 2

Remember, a primary key is a field that uniquely identifies each record in a table.
A foreign key is a field in a table (Child table) and it is also the primary key of another table (Parent table).
Question: Refer to the diagram above, which one is the Parent table and which one is the Child table?
tblMovie is the _________________ table and tblVideo is the ________________ table.

Demonstration:
Test the Referential Integrity.

Key Steps:
(a) Click Database Tools tab and click Relationships
window opens.

in the Relationships group. The Relationships

Can you explain the relationships among these four tables shown in the diagram above?

(b) Delete a customer record from the tblCustomer table when the customer has made a reservation for a video,
i.e. the MemberNo exists in one of the reservation records in the tblReservation table. (You should open the
tblReservation table and pick a customer first.)
What is the error message? _________________________________________________________

Question: Why is Referential Integrity important in a database? ____________________________________

COMM 205 Winter 2015 Term 2

Copyright 2016 Y.M.Cheung

MS Access Tutorial

Page 3

Creating Simple Queries

Queries allow you to select specific records from a table (or from several tables) and show some or all fields for
the selected records. The displayed records may appear in a different sequence from the underlying table.
You may create a query either by using the Query Wizard or specifying the tables and fields directly in the
Query Design View. This visual method of creating database queries is referred to as Query By Example
(QBE).

Selected fields
that will be
displayed in the
query result,
i.e. dataset.

Specify sorting
sequence.
By default, the
records are listed
in primary key
sequence.

Specify the
selection criteria.

The Query Design view consists of two parts. The top portion contains tables with their respective field names.
If a query contains more than one table, the join lines between tables will be displayed as they are created in the
Relationships window.
The bottom portion (known as the Query Design Grid) contains columns and rows. Each field in the query has
its own column and contains multiple rows. The rows permit you to control the query results.
The Field row displays the field name.
The Table row displays the data source.
The Sort row enables you to sort in ascending or descending order.
The Show row controls whether the field will be displayed in the query results.
The Criteria row is used to set the rules that determine which records will be selected, e.g. a list of
movies with video in DVD format.
The results of the query are shown in Datasheet view.
Demonstration
1) Create a query using Query Wizard to list all the movies in the database, sorted by title. (qryMovie)
Key Steps:
(a) Click the Create tab and click Query Wizard

in the Queries group.

(b) Select the Simple Query Wizard in the

New Query dialog box.

COMM 205 Winter 2015 Term 2

Copyright 2016 Y.M.Cheung

MS Access Tutorial

Page 4

Key Steps:
(c) Select tblMovie table in the Query Wizard dialog box.

(d) Select the fields and add them to the Selected Fields list box using the directional arrows.

(e) Choose the Detail query to show every field of every record in the result.
(f) Enter the name of the query, qryMovie.

(g) Open the query to view information. Does it work?

(h) Switch to Query Design view.
(i) Select to sort the query result in Ascending order of Title.

Question: How can we create this query without using Query Wizard? _______________________
COMM 205 Winter 2015 Term 2
Copyright 2016 Y.M.Cheung

MS Access Tutorial

Page 5

Demonstration:
2) Create a query to list the title, length, and synopsis of all movies with the Rating equal to PG. Sort the
result by Title. (qryMovieRating)
3) Create a query to list the title, length, and synopsis of all movies that are categorized as A or B.
(qryMovieCatAB)
4) Create a query to list the title, length, and synopsis of all movies that have DVD format. Sort the result by
Title. Do not display the format field. (qryVideoDVD)

Adding Calculated Fields to a Query

Queries can be used to create a calculated field, i.e. a field that obtains its data by performing a calculation or
computation, using a formula. Sometimes they are called calculated queries. For example, to determine the
profit that will be made from the sale of an item, subtract the cost of the item from the sale price of an item.
Demonstration:
5) Create a query to view all movies and their rental records (qryMovieRental). The query should show the
duration (in days) of each rental, i.e. DateIn DateOut.
Key Steps:
(a) Create a query and add the following fields to the query:
from tblMovie: Title, Rating
from tblVideo: Format
from tblCategoryCode: CategoryDesc
from tblRental: CopyNo, DateOut, DateIn, DueDate, Rate, Discount, Returned
from tblCustomer: LName, FName
(b) Save the query as qryMovieRental;
(c) Open the query in Design View;
(d) In a blank column in the query design grid, type:
Duration: [DateIn]-[DateOut]
(e) Run the query. Does it work?
Note: The name before : will be the name of the calculated field. The expression after : can include any
fields that have been added to the query and other calculated fields.
Demonstration:
6) Modify the query (qryMovieRental) and add a calculated field to show the total charge of each rental, i.e.
Duration * Rate (assuming that the rate is the daily rate) and name the field as Charge.

COMM 205 Winter 2015 Term 2

Copyright 2016 Y.M.Cheung

MS Access Tutorial

Page 6

Creating CrossTab Queries

A crosstab query uses an aggregate function for data that is grouped by two types of information and displays
the data in a compact, spreadsheet-like format.
A crosstab query always has at least one row heading, one column heading, and one summary field, similar to a
Pivot Table in Excel. For example:
Row Headings

Column Headings

Summary Field

Demonstration:
7) Create a crosstab query to show the total rental charges by movie category and video format.
(qryMovieRental_Crosstab)
Key Steps:
(a) Create a query using Query Wizard and select
the Crosstab Query Wizard.

(b) Click Queries option in the middle of the dialog

box. Select query qryMovieRental (the query
that you created in #5 and #6 above1.)
(c) Click Next.
qryMovieRental

(d) Under Available Fields, select CategoryDesc

and Title, and add the fields to the Row
Headings.
(e) Click Next.
CategoryDesc
Title

Row Headings

To save time, you may use the query qryMovieRental_demo if you havent created the query qryMovieRental.

COMM 205 Winter 2015 Term 2

Copyright 2016 Y.M.Cheung

MS Access Tutorial

Page 7

Key Steps:
(f) Select Format field from the fields list, add it
to the Column Headings.
Format

(g) Click Next.

Column Headings

(h) Select Charge field from the fields list and then
select Sum from the Functions list.
(i) Click Next.

Charge

Sum

(j) Name the query as qryMovieRental_Crosstab.

(k) Run the query. Does it work?

(l) Open the query in Design View. Compare this with the Query Result shown on page 7.

Question: What are the limitations of Crosstab Queries? __________________________________________

COMM 205 Winter 2015 Term 2

Copyright 2016 Y.M.Cheung

MS Access Tutorial

Page 8

Practice:
(You may create the following queries after class and you dont need to submit your answers. Suggested
solutions will be discussed in class next week.)
1) Create a simple query to list the movie title, category code, and synopsis of all movies of which length is
greater than 120 minutes and rating is PG. Sort the result by title. (qryMoviePG)
Write down the criteria:__________________________________________________________
2) Create a simple query to list the movie title, length, synopsis, and format of those videos that have at least 3
copies. (qryVideo3Copies)
Write down the criteria:__________________________________________________________
3) Create a simple query to list the video rentals that are two weeks overdue. The list should include the
customer name (last name and first name), customers phone number, movie title, video format, the date the
video is rented out and the due date. (qryRentalOverdue)
(Hint: Use the function Date( ) to retrieve todays date, i.e. system date.)
Write down the criteria:__________________________________________________________
4) Create a crosstab query to show the average rental duration (in days) by movie title and video format.
(qryAverageDuration_Crosstab)
(Hint: Create this query using query qryMovieRental.)
Row Heading(s): ___________________________________________________
Column Heading(s): ________________________________________________
Function: _________________________________________________________

5) Create a crosstab query to show the number of customers by city and customer type.
(qryCustType_Crosstab)
Row Heading(s): ___________________________________________________
Column Heading(s): ________________________________________________
Function: _________________________________________________________
- End of document COMM 205 Winter 2015 Term 2
Copyright 2016 Y.M.Cheung

MS Access Tutorial

Page 9