lesson 31: querying a database

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 Operator

Wildcard 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