Chapter 4
Creating and Using Queries
Notes to the Instructor
In this chapter students will create queries by following step-by-step instructions. Upon finishing
this chapter, each student will gain a thorough knowledge of queries—the objects that supply
Lecture Outline
This chapter assumes you have some hands-on experience using Access and are familiar with
relational databases. Reviewing Chapter 4 in the text will help you use this outline to stimulate
discussion of chapter materials and concepts.
Objectives
• This chapter extends the knowledge gained in previous Access-specific chapters with more
detailed information about creating and running Microsoft Access queries.
• In Chapter 4 the students will learn how to:
➢ Create select and action queries.
➢ Review queries in Design, Layout, and SQL views.
➢ Join tables and establish referential integrity checks between them.
➢ Use the Query Wizard.
➢ Work in Layout View.
➢ Group and summarize data with queries.
Introduction to Queries
• Queries extract information from one or more tables and return dynasets.
4-2 CHAPTER 4 • CREATING AND USING QUERIES
➢ Action queries allow you to change information in one or more tables.
• Queries actively search a specified table, or tables (by joining them), and return answers to
questions in a dynaset.
Views
• There are five query views: Design, Datasheet, PivotTable, PivotChart, and SQL.
• Design view describes the query including columns retrieved, sort order, and filtering
criteria.
• Datasheet view displays the dynaset, or retrieved results.
• PivotTable view displays aggregate information based on two or more variables.
Creating a Basic Select Query
• This section discusses how to use the Access commands to create a query. Review these
bulleted steps with students to make sure they understand the fundamental process.
• Figure 4.2 shows the Coffee Merchant schema.
Retrieving Selected Rows from a Table
Exercise 4.1: Creating a One-Table Query. This exercise shows the students how to create and
run a one-table query using tblInventory. The criterion selects inventory rows in which the
Comments contains the string “hard bean,” and the Beverage is coffee, and the Flavored entry is
“no.” Students learn to use a wildcard criterion for the first time.
Working with a Dynaset
• Query results are returned in a Dynaset.
➢ Rearrange a dyanset’s rows and columns as necessary.
➢ Alter the appearance of dynaset columns by formatting them in limited ways.
Producing Sorted Query Results
• Specifying sort information in a query orders a dynaset’s rows. Any field may be sorted, and
Altering the Order and Size of Columns
• Rearrange dynaset columns in the query definition or in Datasheet view—after a query is
CHAPTER 4 • CREATING AND USING QUERIES 4-3
• Click and drag the column to its new position and release the mouse.
• Dynaset columns can be resized.
Altering column display properties.
• You can alter a small number of dynaset display properties.
• Display the query in Design view.
Saving a Query and Printing Dynasets
Saving a Query
• Save any query that you anticipate using more than once in your database.
• Click Save in the Quick Access Toolbar and name the query.
• Save the query under a different name by clicking Save As in the Office menu.
Printing Dynasets
Creating Queries with Query Wizards
• Query Wizards speed the student through creating some of the more “difficult” queries such
as Crosstab and Find Duplicates.
• Click the Create tab, click Query Wizard, and choose the wizard you want.
Creating Crosstab Queries
• Students create one crosstab query using the Crosstab Query wizard just to get experience
discovering how easy it is to use that wizard.
• The table used to create the query is not related to the other Coffee Merchant tables.
Exercise 4.2: Using the Query Wizard to Create a Crosstab Query. This exercise shows the
students how to create and run a one-table crosstab query using the table
Creating Queries to Find Duplicate Rows
• Students learn how to build a query, using the Find Duplicates wizard, to ferret out duplicate
rows.
4-4 CHAPTER 4 • CREATING AND USING QUERIES
Designing and Using a Parameter Query
• Stress the utility of parameter queries: they allow users to execute a query and then specify
selected criterion at run time
• An exercise runs students through the process
Exercise 4.3: Creating a Parameter Query. This exercise shows the students how create a
parameter query to display customers who reside in a state that the user specifies at run time.
Create a query based on the tblEmployee table (Figure 4.10). Type a question enclosed in
square brackets in the criteria cell of the State field—[Enter a two-character state abbreviation:].
Save and then run the query. When prompted for a state name, enter a two-character state
abbreviation. Only rows corresponding to customers in that state form the dynaset (Figure 4.11).
Setting Query Properties
• Query properties appear in Design view when you click the Property Sheet button or click
Alt+Enter.
• Figure 4.12 shows several of a query’s properties.
• Ensure students understand the Top Values property, because they will be asked to use that in
an end-of-chapter problem.
Working with Multiple-Table Queries
• Joining is the process of connecting or linking tables together using common columns.
• Tables can either be joined manually or automatically.
• Joining Tables Manually.
➢ Add tables to query in Add Table dialog box.
➢ Join fields by selecting the primary key in one table and dragging it to the corresponding
Understanding Table Relationships
• Table relationships define what tables are joined to what other tables.
• Tables that are not in any relationship to any other table in the database are so-called flat
files.
• The relationships are one-to-one (1-1), one-to-many (1-M), and many-to-many (M-M).
CHAPTER 4 • CREATING AND USING QUERIES 4-5
Understanding One-to-One Relationships
Dealing with Many-to-Many Relationships
• Many-to-many relationships occur very frequently.
• The relationship between the tblInvoice and tblInventory tables is many-to-many—for each
record in the tblInvoice table, there are one or more corresponding records in the inventory
table. (Each invoice lists one or more inventory items.)
Using Expressions in a Query
• Queries often contain expressions that include calculations.
• Calculations produce tallies, product mathematical products, perform rounding, and produce
results from table columns.
• Emphasize the necessity to not store results in a table (e.g., quantity X unit cost) that can be
calculated from the table fields.
Using Operators
• Operators—addition, subtraction, multiplication, etc.—are used in expressions in queries
frequently.
Exercise 4.4: Writing a Basic Query. This exercise shows the students how to create and run
a query that joins tables tblInventory and tblInvoiceLine and add columns to the field row of the
4-6 CHAPTER 4 • CREATING AND USING QUERIES
QBE interface. They save the partially complete query so that a subsequent exercise can more
easily refer to fields in the query as it is being further developed.
Using the Expression Builder
Exercise 4.5: Writing an Expression to Compute Extended Price. This exercise shows the
students how to create and run a two-table query that joins tables tblInventory and
tblInvoiceLine and then write an extended price expression (quantity * units * (1-discount))—see
Figure 4.14. Students explicitly add the expression name ExtendedPrice to the front of the
expression. The result shows many decimal places (see Figure 4.15). This is remedied in a
subsequent exercise.
Introduction to Access Built-In Functions
• Access functions provide a rich set of date/time, financial, and math functionality that would
Using Math Functions
• The math function Round is illustrated in an exercise.
Exercise 4.6: Using an Access Built-In Function. This exercise shows the students how to
modify the query in the previous exercise by surrounding the expression with the Round
function. Round allows rounding an expression to any number of decimal places. In this case, it
the value is rounded to two decimal places. Figure 4.16 shows students the new extended
prices rounded to two decimal places.
• Note that students sometimes forget to use closing parentheses or get mixed up, in general,
about where the parentheses belong and why. Write the Round function on the board and use
Exercise 4.7: Formatting Query Columns. This exercise shows the students how to format
the extended price column to display dollar signs and two decimal places. Although the Round
function takes care of rounding the expression, formatting modifies the appearance of the
rounded result, placing a currency symbol at the front and commas where needed.
CHAPTER 4 • CREATING AND USING QUERIES 4-7
Dealing with Null Fields Referenced in Expressions
• Null fields can be a problem when they are referenced in a mathematical expression because
they return an empty value.
Using Date functions
• The Access built-in function date functions are particularly handy for accounting applications
because the DateDiff function, for example, computes month-, week-, and annual differences
between two dates.
Exercise 4.8: Using the DateDiff Function. This exercise shows the students how to use the
round function and how to calculate the difference between two dates and return the answer in
elapsed days.
Using Criteria in Queries
• Criteria limit or filter the results before they are returned.
Exercise 4.9: Creating a Query That Uses a Comparison Operator to Filter Rows. This
exercise shows the students how to formulate criteria that use the comparison operator less
than. Using it filters rows in which there is no inventory (<0 in the UnitsOnHand criteria cell).
Figure 4.21 shows the query design and resulting dynaset.
• Using wildcards in query criteria:
➢ Wildcard characters (Figures 4.22 and 4.23) can help locate information when you are
4-8 CHAPTER 4 • CREATING AND USING QUERIES
Grouping and Summarizing Data
• Access provides several summary functions that you can use in queries, forms, and reports to
produce aggregate information for data groups or an entire table (Figure 4.24).
Exercise 4.10: Using Aggregate Operations in a Query. This exercise shows the students
how to generate sales totals by grouping sales by each salesperson’s name. The Sum function
in the Sales column of the query displays the sum for each employee. Because the Sales
column is sorted in descending order, the highest sale total appears first. Three tables form the
basis of the query: tblEmployee, tblInvoice, and tblInvoiceLine. The query design and dynaset
appear in (Figure 4.25).
Creating and Using an Outer Join Query
• You can join two tables and then display all rows from one table regardless of whether or not
they have a matching row in the other table.
Exercise 4.11: Creating an Outer Join Query. This exercise shows the students how to set up
an outer join relationship (Figure 4.26) to determine which employees (tblEmployee) do not
Building Pivot Table Queries
• Access provides a powerful data analysis tool called a PivotTable.
• The PivotTable tool enables you to review vast amounts of data in a condensed, profile-slice
view.
What is a Pivot Table?
CHAPTER 4 • CREATING AND USING QUERIES 4-9
Pivot Table Terminology
• A field is a category of data such as a region or time period derived from a column in a
source table.
• An item is a subcategory, or member, of a field.
• A data field provides the data values summarized by the pivot table.
Creating One- and Two-Dimensional Pivot Tables
• This section guides students through creating one-dimensional and two-dimensional pivot
tables.
CREATING A ONE-DIMENSIONAL PIVOT TABLE
• Once they have created the query, students create the pivot table in Exercise 4.13.
Exercise 4.12: Creating a Query Used to Build a Pivot Table. This exercise shows the
Exercise 4.13: Creating a Pivot Table from a Query Used. This exercise shows the students
CREATING A TWO-DIMENSIONAL PIVOT TABLE
• Two-dimensional pivot tables provide more information displayed in two or more
Exercise 4.14: Creating a Two-Dimensional Pivot Table. This exercise shows the students
how to create a two-dimensional, displays it in Design view to review the underlying query, and
adds the invoice date to analyze invoices by region and by date. Students learn how easily
Access can provide dates by quarters or months simply by using provided date summary
indicators available in the Field List.
Working in SQL View
SELECT Statement Syntax
• The SELECT statement is the SQL data retrieval statement.
4-10 CHAPTER 4 • CREATING AND USING QUERIES
• This section shows the syntax of the SELECT statement. Students need not memorize its
form.
Self-Join Queries with SQL
Using a Subquery to Find Customers without Invoices
• This section gently introduces students to writing a SELECT statement in the criteria cell of
Exercise 4.15: Formulating Criteria Using a SQL Statement. This exercise shows the
students how to create a query by starting out in the familiar QBE grid. Then, they add a
Finding Employees with Longer than Average Tenure
• Exercise 4.16 leads students step-by-step in creating a complete, stand-alone SQL statement.
• Create a new query, but switch to SQL view immediately; then write the statement and run it.
Exercise 4.16: Finding above Average Length of Service Among Employees. This exercise
shows the students how to write a complete SQL statement to retrieve selected columns from a
single table with date-range criterion and a sorted dynaset. Figure 4.33 shows the retrieved
result.
Creating and Running Action Queries
• Action queries are another group of queries.
Make Table Query
• Create a table from a dynaset (a Select query will do) by defining and executing a Make
Table query.
Update Query
• Update queries allow you to make changes to one or more fields in one or more records in a
table.
CHAPTER 4 • CREATING AND USING QUERIES 4-11
• An update query’s criteria determine which rows receive the changes, and the Update To
QBE grid row indicates the exact change(s) to be made.
• A Try-it exercise increases by 1% the commission rate of female employees who currently
have a commission rate less than or equal to 14% (see Figure 4.35).
Delete Query
• Delete queries delete individual rows from a table.
• Delete queries do not delete a entire table.
• Figure 4.36 shows an example of a query design to remove all invoices prior to a particular
date.
Append Query