Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not

Learning – Not for Sale or Classroom Use

MIC ROS O FT ACCE SS

3 Querying a Database

LY
N
O
N
IO
T
A
LU

One of the main goals of a database is to organize LE A R N I N G OB J EC TIVE S

data so that information can be located and re- ⏵ Create, save, and run select
queries
trieved quickly. People in all types of businesses
⏵ Create select queries using
A

retrieve stored data and information daily, often at multiple tables

a moment’s notice. In this chapter, you will search ⏵ Use simple query criteria
⏵ Use AND and OR criteria in
V

information that is stored in tables in a relational

queries
database and extract records that meet specific cri-
⏵ Use wildcard characters in
E

teria using a query, a database object used to locate query criteria

records based on the conditions you set. ⏵ Sort query results
⏵ Create and format a calculated
field

A3.1
Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

Project: Using Queries to Get Answers

You have been asked to query the Winchester Web Design database and compile two separate
customer lists. The lists will be used to notify all past clients of updates to their website contact
forms. The first list will include only the first and last names of the clients and their email
addresses. The second list will include the first and last names of the clients and their mailing
addresses, sorted by ZIP code. Additionally, you have been asked to build queries that instantly

LY
calculate the total income from all the Winchester Web Design services and from specific areas
such as blogs or shopping carts.

Select Queries

N
A query asks a question, such as, What are the customer addresses? or How much money did the company
make last month? The answer to the question is provided in a set of records.

O
All queries have common attributes:
• They function like a saved question you ask a database.
• They produce a subset of data from one or more tables.

N
• They are dynamic objects that display up-to-date data from tables.
• They can be used to create forms and reports with fields drawn from multiple tables.
• When you edit data in query results, you are actually editing the data stored in the source tables.
IO
A select query is basically a database inquiry that selects only the records you want to see or edit, from
one or more database tables, based on criteria that you set. The easiest way to create a select query is
with the Query Wizard.
T

Create→Queries→Query Wizard
A

DEVELOP YOUR SKILLS: A3-D1

In this exercise, you will use the Query Wizard to create a select query that generates a customer email list.
LU

1. Open A3-D1-WinDesign from your Access Chapter 3 folder and save it as:
A3-D1-WinDesignRev
Notice in the Navigation pane that the database currently has three queries.

Note! When completing exercises, always choose to Enable Content.

A
V
E

A3.2 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

2. Choose Create→Queries→Query Wizard.

The New Query dialog box appears, allowing you to select the Query Wizard you would like. The
Wizard can help you create four types of queries, shown in the right pane of the dialog box.

LY
N
O
3. Click OK to accept the Simple Query Wizard.

N
4. Follow these steps to build the query:
IO
T

A
C
B
A

D
LU

E
A

A. Make sure the Customers table is chosen in the Tables/Queries list. When building a query,
you can use multiple tables and even existing queries.
V

B. Choose the CustLastName field from the Available Fields list.

C. Click the Add button to add it to the Selected Fields list.
E

D. Now add the CustFirstName and CustEmail fields, scrolling to find them as necessary, to
the Selected Fields list.
E. Click Next.
If you add the wrong field, double-click the name to move it back to the Available Fields list or select it
and use the Move Back or Move All Back buttons.

Select Queries A3.3

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

5. Type CustomersEmailList in the query title field at the top of the dialog box.
6. Make sure the Open the Query to View Information option is chosen and click the Finish
button.
Notice the query results datasheet includes only the three fields you chose from the Customers list.

LY
7. Click the Close button at the right of the Customers Email List tab to close the query.

Creating a Select Query Using Query Design View

N
Some queries display just a few fields but report on every single record in the table. That may not be a
problem for a small table, but, when thousands of records and multiple tables are involved, it is often

O
necessary to choose only specific records by setting precise criteria. Using Query Design View, Access
allows you to:
• Select fields from multiple tables
• Locate records using criteria from one or more fields

N
• Perform calculations
• Sort query results and show or hide fields in query results
IO
Create→Queries→Query Design

DEVELOP YOUR SKILLS: A3-D2

T

You have already created an email list for the Winchester Web Design customers and now need one for the
company’s employees. In this exercise, you will create a query to select fields from the Employees table in the
Winchester Web Design database and then rearrange the columns in the query grid.
A

1. Choose Create→Queries→Query Design to display the query design grid.

The Add Tables pane appears on the right, showing the existing tables, links, and queries in the
LU

database.
2. Choose the Employees table and click Add Selected Tables at the bottom of the pane.
The Employees table appears in the design grid.
3. Close the Add Tables pane and close the Property Sheet if it’s open.
Next you will add fields from the Employees table to the grid.
A

4. Double-click the EmpFirstName field in the Employees table to add it to the grid.
5. Now add the EmpLastName, EmpPhone, and EmpEmail fields to the grid by either double-
V

clicking or dragging them.

Tip! Use the scroll bar located at the right of the table fields to access all available
E

fields in the list.

A3.4 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

6. Choose File→Save.
7. Type EmployeeContactInfo as the query name and click OK.
8. Choose Query Design→Results→Run .
Access runs the query and displays four columns of data (First Name, Last Name, Telephone, and
Email) for all Employee records.

Rearrange Query Fields

LY
9. Choose Home→Views→View→Design View.
10. Select the EmpLastName column by placing your mouse pointer over the column heading until
it becomes a downward-facing black arrow and then single-click.

N
O
N
The entire column is selected, shown by shading it in black.
11. Follow these steps to rearrange the EmpFirstName and EmpLastName fields:
IO
C B A
T
A

A. Click the EmpLastName column heading again, this time without releasing the button.
The button will change to a white arrow with a silhouetted box, indicating you can move the
column.
LU

B. Drag the EmpLastName column to the left of the EmpFirstName column until the thick
vertical bar is positioned as shown.
C. Release the mouse button to complete the rearrangement.
12. Run the query.
A

Last Name should now appear first in the results.

13. Close the query and save the changes.
V

Designing a Query Using Multiple Tables

Until now, the query results presented in the datasheets you have worked with have displayed data from
E

only one table. There will be times when you need to view data contained in different tables within the
same database. Multi-table queries allow you to do this.

Select Queries A3.5

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

Choosing Fields to Include in a Query

When you build a multi-table select query, you start in Query Design view. Using the Show Table dialog
box, you can select only those tables and fields that you want to display in the query results datasheet
and leave out those fields that have no impact on the data you want to view or that are confidential. By
specifying only certain tables and fields in a database and displaying only the desired fields in a query,
you can create a report or a form that presents only pertinent data.

LY
Multiple tables are effective in a query only if the tables are related. Using related tables allows a query
to provide results based on all the data contained in the related table fields selected. For example, if you
want to find the names and addresses of customers who placed orders from a specific employee, you
would need fields from both the Customers table and the Invoices table. This is because the Customers
table does not include any Employee fields, and the Invoices table does not include the Customer fields.

N
It would be impossible to answer the question using only the Customers or Invoice tables alone.

☁ View the video “Create a Multi-Table Select Query.”

O
Selecting a Field That Appears in Multiple Tables
Sometimes the same field occurs as a primary key in one table and as a foreign (or secondary) key in
another table. If this occurs, always use the table with the primary key in your query.

Use the Customers table in the query

because CustID is the primary key.

N
IO
T

CustID is a foreign key

A

in the Invoices table.

LU

DEVELOP YOUR SKILLS: A3-D3

In this exercise, you will create a multi-table query using Query Design view to track the Winchester Web
Design invoices by invoice number.
A

1. Choose Create→Queries→Query Design to display the query design grid.

2. Double-click the Invoices, Invoice Details, and Products tables to add them to the query.
V

If the Show Table dialog box is not visible, choose Query Tools→Design→Query Setup→Show Table.
3. Close the Show Table dialog box.
4. Double-click the InvNum, InvDate, and EmpID fields in the Invoices table to add those fields to
E

the query grid.

5. Add the ProdDescription and Price fields from the Products table.

A3.6 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

6. Add the Qty field from the Invoice Details table.

Your query field list should look like this.

LY
7. Click the Sort cell for the InvNum field, click the menu button , and choose Ascending.

N
8. Choose Query Design→Results→Run to run the query.

O
The query results are now sorted by invoice number in the first column.
9. Click the Save button on the title bar; then name the query InvoicesList and click OK to
save the query.

Using Criteria in Queries

N
IO
Queries let you specify criteria, which are conditions that field values must meet. Only records meeting
the criteria are returned when the query is run.
T
A

In this query, the EmpID criteria is set to JFW.

LU
A

Only records where EmpID is JFW are returned.

V
E

Using Criteria in Queries A3.7

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

Criteria are commonly used with text, numeric, currency, and date fields. Review the table for examples
of how criteria are used.

TYPES OF CRITERIA
Field Type Criteria Examples of How Records Are Returned
Text Smith Last name is Smith

LY
> =Smith Last names are from Smith through the end of the
alphabet

Not Smith Last name is not Smith

Numeric & > 123 Numeric value is greater than 123

N
Currency
> =123 Numeric value is greater than or equal to 123

Date Date( ) Date is today’s date

O
< Date( ) – 30 The Date field is 30 days or more prior to today’s date

Tip! Search for Query Criteria in Access help for more criteria examples.

DEVELOP YOUR SKILLS: A3-D4

N
IO
In this exercise, you will add criteria to the query grid and run the query.
1. With the InvoicesList query open, switch to Design View.
2. Click the Criteria cell for the EmpID field.
3. Type JFW and tap [Enter].
T

Access will apply quotation marks indicating this is a literal value.

A
LU

4. Choose Query Design→Results→Run to run the query.

The query results now include only records where the EmpID is equal to JFW.
5. Close the query and choose No when prompted to save the changes.
A

Saving changes to the query at this time would save the JFW criteria as part of the query. However, you
will continue to use the query for all employees.
V

Wildcard Characters
Wildcard characters are used to locate records that have similar but not identical data. They help you
E

locate records that match a pattern. For example, you might want to find all customers with last names
that begin with the letter B or all products that begin with the word Blog.

A3.8 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

COMMON WILDCARD CHARACTERS

Wildcard Symbol How It Is Used
Asterisk (*) Substitutes for a group of characters that appear at the
position of the asterisk
Example: R* in the last name field will locate all last names
beginning with R regardless of how many characters make up

LY
the name. In this case, Rogers, Rich, and Rodriquez would all
appear in the results datasheet.

Question mark (?) Substitutes for a single character that might appear at the
position of the question mark
Example: m?s will locate records containing values such as

N
mrs, ms, and mbs.

Open/close brackets [ ] Matches text or individual characters placed within the

O
brackets individually
Example: ca[rt] will find cat and car but not cab or cad.

Tip! Search for wildcard characters in Access help for more wildcard symbols and exam-
ples.

AND and OR Criteria

N
IO
In some cases, you may need to locate records that meet multiple criteria. This can be done using AND
and OR conditions. For example, you may want to locate all records where the employee is web certified
AND lives in Sarasota. Or you may want to locate all employees who live in Sarasota OR Bradenton.
T
A

Create an OR condition by adding a second criterion to the Or row of a field.

LU
A

Create an AND condition by adding another criterion to a different field on the Criteria row.

☁ View the video “Create a Query with Criteria.”

V
E

Using Criteria in Queries A3.9

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

DEVELOP YOUR SKILLS: A3-D5

In this exercise, you will use wildcards to locate variable data and set multiple criteria in a query to find out
which customers have gotten blogs and which customers have added more than ten images at a time to their
websites.
1. Open the InvoicesQuery query in Design View.

LY
2. Follow these steps to use wildcard characters and to use AND and OR criteria:

A
B C

N
A. In the ProdDescription Criteria cell, type Blog* and tap [Enter].
Access converts Blog* to: Like “Blog*”

O
B. In the ProdDescription Or cell, type Image* and tap [Enter].
C. Enter > 10 in the Qty Or cell. Be sure to type in the same row as Like “Image*” (the Or row).
These criteria will choose records where ProdDescription begins with Blog OR ProdDescription begins

N
with Image AND the Qty is greater than 10.
3. Run the query.
Access displays the records that meet the specified criteria: either a blog or a transaction with more
IO
than ten images.
4. Close the query and save the changes.

Date Criteria
T

You can set date criteria to determine age, hired date, invoice date, and so forth. Access acknowledges
the same comparison criteria for performing date comparisons that it does for locating other types of
A

data, regardless of the format used to enter dates.

DATE CRITERIA
LU

Criterion Examples of How Records Are Returned

06/22/2021 Date is 06/22/2021

<22-Oct-2021 Dates that occur before 22/Oct/2021

A

>01/01/21 Dates that occur after 01/01/21

Between 01/01/21 and 06/30/21 Dates between 01/01/2021 and 06/30/2021

V
E

A3.10 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

DEVELOP YOUR SKILLS: A3-D6

Winchester Web Design needs to track all invoices issued in 2031. In this exercise, you will query the database
to locate customers with invoices dated from January 1, 2031, through December 31, 2031.
1. Choose Create→Queries→Query Design .
2. If necessary, open the Add Tables pane and click the Tables tab at top left of the pane.

LY
3. Use double clicks to add the Customers, Invoices, InvoiceDetails, and Products tables to
the query.
4. In the Invoices table double-click InvNum and InvDate to add those fields to the query grid.
5. From the Customers table add the CustID field.
6. From the Invoice Details table add the Qty field.

N
7. From the Products table add the ProdDescription and Price fields.
8. Hover your mouse pointer on the right edge of the InvDate column heading so a black, two-

O
direction arrow appears.

N
9. Click and drag the column heading to the right until the column is about three times the original
IO
width.
You will enter a long entry in the next step and widening the InvDate column will allow you to see the
entire entry.
10. Click in the Criteria cell for the InvDate field and type: Between January 1, 2031 And
T

December 31, 2031

Access formats the expression. Your query grid should now match this example. Regardless of how you
A

type the dates—whether January 1, 2031; 01/01/31; or 1-1-2031—Access formats the data after you
enter it so it appears as #1/1/2031#.
LU

11. Run the query.

A

Notice that only records with a date in 2031 appear in the results.
12. Choose File→Save.
13. Save the query as Invoices2031 and then close it.
V
E

Using Criteria in Queries A3.11

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

Sorting, Showing, and Limiting Results

The query grid contains a Sort row that lets you sort the query results. At times you may also want to
use fields to specify criteria but may not want those fields to be displayed in the query results. This can
be accomplished by unchecking the Show box for the desired field(s).

Limiting the Number of Results Displayed

LY
Large databases with thousands of records often return so many records that it can be challenging to
find what you are looking for. Limiting the number of records displayed can be beneficial, especially
when these records are sorted.
For example, if you set up a query to sort in descending order and then limit the number of items

N
displayed to ten, you would, in effect, have a list of the top ten items in the table being queried. The
Return feature lets you set the number of records to be displayed, or returned, in the query results.

O
Query Design→Query Setup→Return

DEVELOP YOUR SKILLS: A3-D7

N
In this exercise, you will create a query that sets a sort order and hide a field from displaying in the query
results. You will also limit the number of records returned.
IO
1. Choose Create→Queries→Query Design.
2. If necessary, open the Add Tables pane and click the Tables tab at top left of the pane.
3. Use double clicks to add the Customers, Invoices, Invoice Details, and Products tables to the
query.
T

4. In the Customers table double-click the CustID, CustFirstName, and CustLastName fields to
add them to the design grid.
5. From the Invoices table add the InvDate field.
A

6. From the Products table add the ProdDescription field.

7. From the Invoice Details table add the Qty field.
LU

8. Follow these steps to set a criterion and set the sort order:

B
A
A

A. In the ProdDescription Criteria cell, type Image* and tap [Enter]. Access converts Image* to
V

Like “Image*”. This criterion will choose only records where the product description begins
with Image.
B. In the Sort cell for the Qty field, choose Descending from the list of sort options.
E

A3.12 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

9. Run the query.

The records are now sorted in descending order (largest to smallest) by quantity.
CustID is an important key to have in the query because it is a primary key field. But it isn’t needed
in the query results because it contains the same information that appears in the CustFirstName and
CustLastName fields, so you will hide it from the query results.
10. Choose Home→Views→Design View to switch back to Design View.

LY
11. Uncheck the Show box for the CustID field and Run the query.
The CustID field is still part of the query design, but it no longer shows in the query results.
12. Switch back to Design View and choose Query Design→Query Setup→
Return menu button .
13. Choose 5 from the list and run the query.

N
The query returns seven records (not five). This is because the query returns all records with the five
largest quantities. But three records had a Qty of 14, which is the fifth highest amount, so all those
records were returned, increasing the total to seven records.

O
14. Save the query as MostImages and then close it.

Calculated Fields

N
Calculated fields are fields containing formulas that perform calculations. Formulas used in calculated
fields are often based on other fields within the query. Calculated fields are added as an additional field
IO
to a query and are not part of the underlying query tables. They are added to the query design grid and
their calculated results then appear in the query results. A calculated field:
• Creates a new field in a query that can also be used in a form or report
• Can be used to perform mathematical operations, such as addition and multiplication
T

• Has a name and can be formatted with properties just like a regular field
• Enables you to combine values in two text fields into one field, such as LastName and FirstInitial
A

• Updates and recalculates each time you run the query

Identifying Parts of a Calculated Field

LU

The structure of a calculated field includes a field name and a mathematical expression. An example of
a calculated field in an Access query is Wage: 12.00 * 40, where Wage is the calculated field name and
12.00 * 40 is the calculation to be performed. Another example is Total: Price * Quantity, where Total is
the calculated field name and Price * Quantity are the calculations performed using the data in those
query fields.
A
V
E

The LineTotal calculated field multiplies Price * Qty. The query results.

Calculated Fields A3.13

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

Each calculated field can contain the following elements:

CALCULATED FIELD ELEMENTS

Element Description
Calculated field name This is the unique name you assign to the field and is
followed by a colon (:) to separate the field name from the

LY
expression.

Field names from Field names from the query can be added to the calculated
existing tables field expression. Access adds brackets [ ] around field
names.

Arithmetic or Use +, -, *, /, ( ), ^, <, =, and > to perform mathematical

N
comparison operators operations or compare values.

Concatenation (i.e., An ampersand (&) can be used to join text values from
linking together) multiple fields. For example, FirstName&LastName.

O
Spaces can be added between fields by using quotation
marks around a single space (" "). For example, the quotation
marks in FirstName& " " &LastName create a space between
the first and last names in the query results.

Calculated Field Properties

N
IO
You can set field properties such as size, number format, and default values within tables. Likewise, you
can set field properties in calculated fields. This is almost always needed in calculated fields as the query
results need to be formatted with the correct number of decimal places, commas, currency format, and
other formatting as needed. Field properties are set using the field Property Sheet.
T

Query Design→Show/Hide→Property Sheet

A

DEVELOP YOUR SKILLS: A3-D8

LU

In this exercise, you will create and format a calculated field.

1. Open the InvoicesList query and switch to Design View.
2. Click in the first cell of the blank column next to the Qty field.
A
V

3. Type the calculated field expression, taking care to include the colon between LineTotal and
Price: LineTotal:Price * Qty
E

A3.14 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

4. Tap [Enter] to accept the entry and allow Access to format the expression by adding brackets to
the field names.
Access does not always format your expression by adding brackets to field names. Brackets are
required for Access to identify the entry as a field within the query. If you want to use field data within
a calculated field expression, you need to include the name of your calculated field within brackets. In
this example our calculated field is named LineTotal, and it will multiply the data in the Price field by
the data in the Qty field in each record when the query is run. Your completed field should match the

LY
following.

N
5. Right-click anywhere in the column of your calculated field and choose Properties.
The Property Sheet opens.

O
6. Click the Format field, then click the menu button and choose Currency from the list.
7. Click the Caption field and type: Line Total
The currency format will display the calculated results with a dollar sign and two decimals. The

N
caption will become the column heading for your calculated field in the query results.
8. Run the query, and your calculated field results will appear as shown:
IO
T
A

9. Save and close the query and then close the database.
LU

Self-Assessment
Check your knowledge of this chapter’s key concepts and skills using the Self-Assessment in your ebook
or online (eLab course or Student Resource Center).
A
V
E

Calculated Fields A3.15

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

Reinforce Your Skills

REINFORCE YOUR SKILLS: A3-R1

Create Queries Using Criteria and Wildcards

Kids for Change is planning to fine-tune its database by adding queries that enable it to track activities as

LY
well as staff/volunteer availability. In this exercise, you will create various queries that will yield the desired
information.

1. Open A3-R1-K4C from your Access Chapter 3 folder and save it as: A3-R1-K4CRev
2. Choose Create→Queries→Query Wizard.

N
3. Choose Simple Query Wizard and click OK.
4. Add the Activity, Location, Day, and MeetTime fields from the Activities table to the Selected
Fields list and click Next.

O
5. Name the query ActivitiesList and click Finish.
6. Review the query results and then close the query.

Create a Query in Design View

N
7. Choose Create→Queries→Query Design to start a new query.
8. Add the Volunteers table.
IO
9. Add the VolLastName, VolFirstName, VolPhone, and VolDay fields to the query design grid (in
that order).
10. Run the query and take a moment to review the results.
Now you will change the field order.
T

11. Switch to Design View.

12. Click and drag the VolDay field, dropping it in front of the VolLastName field.
A
LU

13. Run the query and review the results.

You may notice the field names appear differently from those used in the query grid. Field names in
queries retain any caption labels previously set in table properties.
A

14. Save the query as VolunteerList and then close the query.
V
E

A3.16 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

Create a Multi-Table Query

15. Create a new query in Query Design view, add the Activities and Staff tables to the query.
16. Move the fields from the indicated tables to the query design grid:

From This Table Add These Fields

Activities • Activity

LY
• Day
• MeetTime

Staff • StaffLastName
• StaffFirstName
• StaffPhone

N
17. Set the Sort option for the Activity field to Ascending.
18. Run the query and view the results.

O
19. Save the query as ActivityStaffingList and then close it.

Add Wildcard and AND/OR Criteria to a Query

N
20. Right-click the ActivityStaffingList query in the Navigation pane and choose Design View.
You can open a query in Design View using this method or you can run it first and then switch to
Design View. Remember to try right-clicks if you are having trouble finding commands.
IO
21. Create a Saturday or Sunday OR condition in the Day field.
Typing the quotation marks " " isn’t necessary, as Access will add them for you.
T
A

22. Run the query.

Only activities for Saturday or Sunday should be displayed.
LU

23. Switch to Design View and delete Sunday to remove the OR condition.
24. Enter 12:00 in the MeetTime Criteria field, tapping [Enter] when finished.
This creates a Saturday AND 12:00 meet-time condition. Access will format the 12:00 condition like
this: #12:00:00 PM#
A
V

25. Run the query.

E

Because both conditions must be met, just one activity meeting (a car wash) should be returned by the
query.
26. Switch to Design View and remove both the Saturday and 12:00 criteria.

Reinforce Your Skills A3.17

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

27. Type S* in the Criteria cell for the Day field and tap [Enter].
Access recognizes the asterisk * wildcard character and formats the condition as Like “S*”. The query
will return all records where the name of the day begins with S (Saturday and Sunday) and should
produce the same results as when you used the Saturday OR Sunday condition earlier in this exercise.
28. Run the query and take a moment to observe the results.
29. Save the changes and close the query.

LY
Add Date Criteria to a New Query
Now you will create a query that returns the records of the youngest children so you can determine which
children may need more supervision.

30. Create a new query in Design View, adding the Children table and the fields ChildLastName,

N
ChildFirstName, and BirthDate.
31. Run the query and take a moment to observe the results.

O
Now you will add a condition.
32. Switch to Design View, type >January 1, 2020 in the BirthDate Criteria field, and
tap [Enter].
Once again Access will apply formatting to the criterion.

N
33. Run the query.
Only records where the child was born after January 1, 2020, should be displayed.
IO
34. Choose File→Save and save the query as: YoungerChildren
35. Close the query and then close the database.

REINFORCE YOUR SKILLS: A3-R2

T

Limit the Records Returned and Use Calculated Fields

Kids for Change is planning to fine-tune its database further by adding queries that will produce calculated
A

results. You are in charge of the IT department. In this exercise, you will generate the desired query results.

1. Open A3-R2-K4C from your Access Chapter 3 folder and save it as: A3-R2-K4CRev
LU

2. Run (open) the ChildrenList query.

The query returns the records of all children in the database in alphabetical order by last name.
3. Switch to Design View and choose Query Design→Query Setup→Return menu button .
4. Choose 5 from the list.
A

5. Click in the Sort cell for the BirthDate field and choose Descending.
6. Run the query.
Only the records for the five youngest children should be displayed.
V

7. Close the query, saving the changes.

Add a Calculated Field and Format the Field

E

As part of its community give-back policy, Kids for Change puts 10% of all donations into a scholarship
fund. Now you will add a field that calculates 10% of each donation.

8. Run the DonationsQuery query and take a moment to observe the results.

A3.18 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

9. Switch to Design View and use the scrollbar at the bottom of the grid to scroll the query grid to
the right until the first empty column is visible.
You will enter a calculated field in this column.
10. Type ScholarFund:Amount*.1 in the first cell (the Field cell) of the empty column, being
sure to include the colon between ScholarFund and Amount.
11. Tap [Enter] to complete the calculated field, and if necessary, widen the column so you can see

LY
the entire calculated field.
12. If the Property Sheet is not open, right-click anywhere in your calculated field column and
choose Properties.
13. Click in the Format field and choose Currency from the drop-down list.
14. Type Scholar Fund in the Caption field.

N
15. Run the query and take a moment to ensure that the calculated field is calculating correctly and
is formatted with the Currency format.
16. Close the query, saving the changes, and then close the database.

O
REINFORCE YOUR SKILLS: A3-R3

Create Select Queries Using Criteria and Calculated Fields

N
In this exercise, you will help Kids for Change further develop its database by adding queries that will
produce calculated and formatted results based on specific search criteria.
IO
1. Open A3-R3-K4C from your Access Chapter 3 folder and save it as: A3-R3-K4CRev
2. Use the Query Wizard to create a simple query using the Donors table and the DonorLName,
DonorFName, DonorPhone, and DonorEmail fields.
3. Use DonorContactList as the query name and finish the query.
T

4. Review the query results and then close the query.

5. Using Query Design, create a new query with the Staff table and the StaffLastName,
A

StaffFirstName, StaffStreet, StaffCity, StaffST, and StaffZIP fields.

6. Save the query as: StaffMailingList
LU

7. Run the query, review the results, and then close the query.

Create a Multi-Table Query

8. Create a new query using Query Design view and add the Activities and Children tables to the
design grid.
A

9. Move the fields from the indicated tables to the query design grid:

From This Table Add These Fields

V

Activities • Activity
• Day
• MeetTime
E

Children • ChildLastName
• ChildFirstName
• ChildPhone

Reinforce Your Skills A3.19

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

10. Save the query as: ParticipantList

11. Run the query and review the results.

Add Criteria Including Wildcards and Dates

Now you will add criteria to the ParticipantList query to list the children signed up for 9:00 AM Saturday
activities.

LY
12. Switch to Design View.
13. Create an AND condition by setting Saturday as a criterion in the Day field and 9:00 as a
criterion in the MeetTime field.
14. Run the query.

N
The only records returned are those where the day is Saturday AND the meet time is 9:00.
15. Close the query, saving the changes.
Now you will use a wildcard to select nearby donors so they can be invited to local activities.

O
16. Right-click the DonationsQuery query in the Navigation pane and choose Design View.
17. If necessary, scroll right through the field list until you locate the DonorZIP field.
18. Enter 34* in the DonorZIP Criteria field.

N
The asterisk is a wildcard character.
19. Run the query.
Only records where the ZIP code begins with 34 are returned by the query.
IO
20. Switch to Design View and remove the criteria from the DonorZIP field.
21. Enter >01/01/2031 in the DonationDate Criteria field.
22. Run the query and review the results.
T

Sort and Limit Query Results

23. Switch to Design View.
A

24. Set the DonationDate field to sort in Descending order.

25. Use the Query Design→Query Setup→Return menu button list to limit the records
returned to 5.
LU

26. Run the query and review the results.

Add a Calculated Field and Format the Field

27. Switch to Design View and set the Return number back to All.
A

28. Create a calculated field by entering NetAmt:Amount-ScholarFund in the first empty

column’s Field row.
29. Right-click in the new calculated field column and open the Property Sheet.
V

30. Set the Format to Currency and type Net Donation as the Caption.
31. Run the query and review the results.
E

32. Close the query, saving the changes, and then close the database.

A3.20 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

Apply Your Skills

APPLY YOUR SKILLS: A3-A1

Create Queries Using Criteria and Wildcards

The new CEO of Universal Corporate Events has asked you to refine a number of queries to be more selective

LY
in data output. In this exercise, you will create queries; add criteria, wildcards, and AND/OR conditions to a
query; and add date criteria to a query.

1. Open A3-A1-UCE from your Access Chapter 3 folder and save it as: A3-A1-UCERev
2. Use the Query Wizard and this table to create a simple select query:

N
Table to Use Fields to Add Query Name
Personnel • PerLastName • PersonnelContactList

O
• PerFirstName
• PerPhone
• PerEmail

N
3. Review the results and then close the query.
4. Create a query in Design View that uses the tables and fields indicated:
IO
From This Table Use These Fields
Events • EventName

Schedules • VenueID
T

• ContactID
• EventDate
A

• Guests

Menus • MenuPlan
LU

• Chg/PP

5. Run the query and review the results.

6. Save the query as EventList and then close it.

Use Wildcards and AND/OR Criteria

A

UCE is planning a recruiting event in Sarasota and would like to contact employees from greater Sarasota
(area code 941) to involve them in the planning. You will modify a query to return the records of personnel
V

who live in the Sarasota area.

7. Open the PersonnelContactList query in Design View.

E

8. Enter the wildcard text *941* in the PerPhone Criteria field.

9. Run the query and verify that each telephone number in the query results contains 941
somewhere in the number.
10. Close the query, saving the changes.

Apply Your Skills A3.21

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

11. Create a new query in Design View from the Venues table that includes the VenueName,
VenueCity, VenuePhone, and VenueWebSite fields.
12. Enter Sarasota in the VenueCity Criteria field and Tampa in the Or row of the VenueCity field.
13. Run the query and verify that the city is Sarasota or Tampa in each record.
14. Save the query as TampaSarasotaVenues and then close it.

Add Date Criteria

LY
15. Run the EventList query and notice the range of dates.
16. Switch to Design View and type >May 1, 2031 in the EventDate Criteria field.
17. Sort the query in Ascending order on the EventDate field.
18. Run the query and make sure it produces the intended results.

N
19. Close the query, saving the changes, and then close the database.

O
APPLY YOUR SKILLS: A3-A2

Limit the Records Returned and Use Calculated Fields

You’ve been asked to improve Universal Corporate Events’ data retrieval and formatting. In this exercise,

N
you will sort and limit records returned in query results and create a query using a calculated field.

1. Open A3-A2-UCE from your Access Chapter 3 folder and save it as: A3-A2-UCERev
IO
2. Run the EventRevenue query and review the results.
3. Switch to Design View and set the sort order of the TotalRev calculated field to Descending.
4. Set the Return number to 5 to limit the number of records returned by the query to the top five.
5. Run the query and review the results.
T

6. Switch to Design View and change the Return value back to All.

Add a Calculated Field and Format the Field

A

7. Create a new calculated field using the name and the expression: Comm:TotalRev*.08
8. Open the Property Sheet for the new calculated field and set the Format to Currency and use
LU

Commission as the Caption.

9. Run the query and review the results.
10. Close the query, saving the changes, and then close the database.

APPLY YOUR SKILLS: A3-A3

A

Create Select Queries Using Criteria and Calculated Fields

V

In this exercise, you will create and modify a number of queries for more precise, targeted data selection for
Universal Corporate Events.
E

1. Open A3-A3-UCE from your Access Chapter 3 folder and save it as: A3-A3-UCERev
To begin, you will create a query to list contact information for the event venues that have an 800
telephone number so they can be reached by phone at no charge to the caller.
2. Create a simple query named TollFreeVenues that uses the Venues table to generate a list
of venue names and their corresponding phone numbers and websites.

A3.22 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

3. In Design View, add the wildcard text *800* to the Criteria row to return only records for
which the venue phone number includes 800.
4. Run the query and resize the columns in the query results so all data is visible.
5. Close the query, saving the changes.

Add Wildcard and Date Criteria and Sort the Query

LY
Because June is the most popular month for weddings, UCE wants to pay special attention to weddings
scheduled for June so they can hire extra part-time workers.

6. Using the Query Wizard, create a simple query that uses all fields from the EventList query.
7. Leave the Wizard’s Detail or Summary option set to Detail.

N
8. Name the query JuneWeddings and finish the query.
9. Switch to Design View.
10. Add the wildcard text Wed* (for Weddings) to the EventName Criteria field.

O
11. Set the sort order of the EventDate field to Ascending.
12. In the EventDate Criteria field, enter: Between June 1, 2031 And June 30, 2031
13. Run the query and review the results.

N
14. Close the query, saving the changes.

Limit the Number of Records in Query Results

IO
Now you will sort the Location Scheduling query by the largest number of guests and return the ten highest
values so the company can focus extra personnel and resources to those events if the guests are scheduled for
a full menu plan.

15. Display the LocationScheduling query in Design View.

T

16. Sort the query in Descending order by Guests.

17. Set the number of records returned to: 10
A

You’ll need to click in the Return cell and type 10. If Access changes the 10 to 100, delete the extra
zero (0).
LU

18. Run the query and review the results.

19. Close the query, saving the changes.

Add and Format Calculated Fields

Now you will add a calculated field that subtracts the venue contact’s commission from the total revenue to
A

result in a net revenue amount.

20. Display the EventRevenue query in Design View.

V

21. Add a calculated field named NetRev that subtracts Comm from TotalRev.
22. Format the new field as Currency and set the Caption as: Net Revenue
E

23. Add a criterion to the TotalRev field to choose only records where the TotalRev is greater
than 3000.
24. Run the query and review the results.
25. Close the query, saving the changes, and then close the database.

Apply Your Skills A3.23

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

Project Grader
If your class is using eLab ([Link]), you may upload your completed Project Grader assignments
for automatic grading. You may complete these projects even if your class doesn’t use eLab, though you will
not be able to upload your work.

PROJECT GRADER: A3-P1

LY
Taylor Games: Creating Queries
Taylor Games wants to evaluate where levels of inventory are too high. You will use the inventory data to
create several queries, leverage wildcard characters, and add a calculated field.

1. Download and open your Project Grader starting file.

N
• Using eLab: Download A3_P1_Query_eStart from the Assignments page. You must start with
this file or your work cannot be automatically graded.

O
• Not using eLab: Open A3-P1-Query from your Access Chapter 3 folder.
2. Use Query Design to create a simple query using the following guidelines:
• Add all fields from the Inventory table in the same sequence as they appear in the table.
• Set the criteria to include all records where (Quantity is greater than 75) AND (TotalCost is

N
greater than 250).
• Save the query as Overstock and run it.
IO
3. Add criteria to the Overstock query so that the query logic now becomes (Quantity is greater than
75 AND TotalCost is greater than 250) OR (Item contains the word dice AND Quantity is greater
T

than 75) and then run the query.

A
LU

4. Add a calculated field named OverstockQty that subtracts 75 from the Quantity field in each
record.
5. Sort the query results on the OverstockQty field in descending order.
A

6. Save and close the query.

V

7. Save your database.

• Using eLab: Save it to your Access Chapter 3 folder as A3_P1_Query_eSubmission and
attach the file to your eLab assignment for grading.
E

• Not using eLab: Save it to your Access Chapter 3 folder as: A3-P1-QueryRev

A3.24 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

PROJECT GRADER: A3-P2

WebVision: Querying a Database

WebVision would like to create a monthly query that will calculate the Sales Rep commissions for each order.
You will use the data in multiple tables to create a select query and add a calculated field.

1. Download and open your Project Grader starting file.

LY
• Using eLab: Download A3_P2_Query_eStart from the Assignments page. You must start with
this file or your work cannot be automatically graded.
• Not using eLab: Open A3-P2-Query from your Access Chapter 3 folder.
2. Use the Query Wizard to create a simple query using the following guidelines:

N
• Add the RepID, LastName, and SalesTeam fields from the SalesReps table in that sequence.
• Add the OrderID, Date, and Amount fields from the Orders table in that sequence.
• Use the Detail option.

O
• Name the query: JuneCommissions
3. Set the Date criteria to include records between 6/1/2019 And 6/30/2019.
4. Sort the query results on the RepID field in Ascending order.

N
5. Add a calculated field named Commissions that multiplies the Amount field in each record
by: 0.02
6. Apply the Currency number format to the Commissions field and then run, save, and close the
IO
query.
T
A
LU

7. Save your database.

• Using eLab: Save it to your Access Chapter 3 folder as A3_P2_Query_eSubmission and
attach the file to your eLab assignment for grading.
• Not using eLab: Save it to your Access Chapter 3 folder as: A3-P2-QueryRev
A
V
E

Project Grader A3.25

Labyrinth Learning [Link]
Building a Foundation with Microsoft Office 2021 & 365 FOR EVALUATION ONLY © 2022 Labyrinth Learning – Not for Sale or Classroom Use

Extend Your Skills

These exercises challenge you to think critically and apply your new skills in a real-world setting. You will be
evaluated on your ability to follow directions, completeness, creativity, and the use of proper grammar and
mechanics. Save files to your chapter folder. Submit assignments as directed.

A3-E1 THAT’S THE WAY I SEE IT

LY
Blue Jean Landscaping needs queries to better manage its customer and equipment lists and you’ve
volunteered to assist. Open A3-E1-BJL and save it as: A3-E1-BJLRev
Create a query named 813AreaCode that uses all fields from the Customers table. The query should
return only customers with a phone area code of 813 sorted in ascending order by city. Create another

N
query named EquipmentValue that uses all fields from the Equipment table. Use a calculated
field named EquipValue to determine the total value of equipment by multiplying the Cost by the
quantity In Stock. Sort the results with the largest Equipment Values appearing first and format the

O
EquipValue field using the Currency format.

A3-E2 BE YOUR OWN BOSS

N
Blue Jean Landscaping wants to devise more targeted data retrieval. Open A3-E2-BJL and save it as:
A3-E2-BJLRev
Create a query that returns a contact list for BJL’s customers sorted by last name. Create another query
IO
that creates a customer mailing list sorted by ZIP code. Use a wildcard to select only records where
the ZIP code begins with 33. Add a calculated field to the Sales Invoices query that multiplies Cost by
Qty Sold to produce a total. Format the new field as Currency and assign it a caption. Finally, limit the
number of records returned to the largest five invoice totals, so those customers can be targeted for
T

preferred customer offers.

A

A3-E3 DEMONSTRATE PROFICIENCY

You’ve been asked by the management at Stormy BBQ to query its database. Open A3-E3-StormyBBQ
LU

and save it as: A3-E3-StormyBBQRev

Create a query that uses data from the DailyReceipts table and determines the total revenue received
for each item using the ItemPrice and QtySold fields. Include all fields from the table in
the query and sort in descending order on the field that is used to perform the daily total calculations.
Create another query using the Merchandise table that contains all fields from the Merchandise table
A

and a sequence of calculated fields. For each item, the calculated fields should determine the Stock Cost
of that item (Cost * Stock), the List Price Revenue if all items were sold at list price (Listprice * Stock),
and the Profit, which is the difference between the revenue and cost.
V
E

A3.26 Microsoft Access 2021 Chapter 3: Querying a Database

Labyrinth Learning [Link]