Lesson 31: Querying a Database

2

Learning Objectives

After studying this lesson, you will be able to:

Create, save, and run select queries

Design a query using multiple tables

Set query criteria

Define a query sort order

Create and format a calculated field

Use functions in query expressions

Create a crosstab query

Create unmatched and duplicates queries

Queries Defined

Queries are database objects that enable you to ask

questions of a database in order to retrieve data and

extract records that meet specific criteria

3

Reviewing Query Features Query results datasheet data remains stored in its

original table rather than in the query Data edited in a query results datasheet changes data

stored in a table Queries are dynamic objects that display up-to-date

data stored in database tables Queries can be used to create forms and reports

containing fields from multiple tables Query results datasheets enable you to filter or

organize data using the same techniques you use to filter and organize table datasheets

4

Creating Select Queries

Access contains two distinct tools for creating select

queries:

Query Wizard

Design View

5

Using the Simple Query Wizard

Steps you through the creation of a select query Enables you to identify table(s) and fields from each

table that you want to include in the query Prompts you to save the query

6

Using Query Design View Presents a split window with table field lists at the top

and columns and rows at the bottom

7

Adding Fields to the Query Grid Double-click a field name to add the field to the next

available column of the query design grid Drag a field to the next column in the grid Click the Field row of a column in the query grid and

selecting the field from the drop-down list Double-click the asterisk (*) that appears at the top of

the field list to add all fields to the grid Double-click the field list title bar to place each field in

the table in a separate column of the query grid.

8

Note! When you use the asterisk to add all fields, Access places

the table name in field row, but when you run the query, each field

appears in a separate column of the query results datasheet.

Setting Query Criteria

9

Comparison

OperatorWildcard Value Dates Wildcard

10

Setting AND and OR Criteria

AND Criteria OR Criteria

Sorting a Query and Limiting Results

Sorting Query Results

Using sort tools after running the query

Setting a sort order in the query grid Sort row

11

Limiting Results Effective way of limiting results combines sorting with

restricting the number of results to display The Return feature on Query Design tab enables you

to set the number of records you want to retrieve

12

Please Excuse My Dear Aunt Sally

Parentheses

Exponentials

Multiplication/Division

Addition/Subtraction

(2+2)*32=6/2=33

13

Calculated Fields

Have no value of their own

Perform calculations using other field values

Calculated

field name

Field names from

existing tables

Arithmetic or

comparison

operator

14

Using Functions in Queries Groups query results by field on which a sum or

average or other order is required Multiple fields and calculated fields can be used for

grouping Example shows minimum, maximum, and average

15

Crosstab Queries

Rearranges data for summarizing

Groups data and totals values

16

17

Crosstab Query Palette

Groupings appear as row and column headings

Summarized values appear in the TOTAL area

Unmatched and Duplicates Queries

Checks database tables to ensure there is no

duplication of records

Also checks database to ensure that records

referenced in one table have a matching record in the

other table

Hoped-for result of running these two queries is that

Access finds nothing to report

18

Structured Query Language

The underlying structure created for queries in Access

19

Lesson 31: Querying a Database