Shelly Cashman: Microsoft Access 2019

Module 2: Querying a Database

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in
whole or in part, except for use as permitted in a license distributed with a certain product or
service or otherwise on a password-protected website for classroom use. 1
 Objectives (1 of 2)
• Create queries using Design view
• Include fields in the design grid
• Use text and numeric data in criteria
• Save a query and use the saved query
• Create and use parameter queries
• Use compound criteria in queries
• Sort data in queries

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 2
otherwise on a password-protected website for classroom use.
 Objectives (2 of 2)
• Join tables in queries
• Create a report and a form from a query
• Export data from a query to another application
• Perform calculations and calculate statistics in queries
• Create crosstab queries
• Customize the Navigation Pane

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 3
otherwise on a password-protected website for classroom use.
Project—Querying a Database (1 of 4)

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 4
otherwise on a password-protected website for classroom use.
Project—Querying a Database (2 of 4)

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 5
otherwise on a password-protected website for classroom use.
Project—Querying a Database (3 of 4)

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 6
otherwise on a password-protected website for classroom use.
Project—Querying a Database (4 of 4)

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 7
otherwise on a password-protected website for classroom use.
 Creating Queries (1 of 14)
• To Create a Query in Design View
• Click the “Shutter Bar Open/Close Button” to close the
Navigation Pane
• Click CREATE on the ribbon to display the CREATE tab
• Click the Query Design button to create a new query
• Click the table to add to the query
• Click the Add button to add the selected table to the query
• Click the Close button to remove the dialog box from the screen
• Drag the lower edge of the field list down far enough so all
fields in the table appear

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 8
otherwise on a password-protected website for classroom use.
 Creating Queries (2 of 14)

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 9
otherwise on a password-protected website for classroom use.
 Creating Queries (3 of 14)
• To Add Records to the Database
• Open the table in Datasheet view, then close the Navigation Pane
• Click an open cell
• Enter the data
• Close the table

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 10
otherwise on a password-protected website for classroom use.
 Creating Queries (4 of 14)
• To Add Fields to the Design Grid
• Double-click each field to add to the query

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 11
otherwise on a password-protected website for classroom use.
 Creating Queries (5 of 14)
• To Use Text Data in a Criterion
• Click the Criteria row for the field to produce an insertion point
• Type the criterion
• Click the Run button to run the query
• Click the Save button on the Quick Access Toolbar to display
the Save As dialog box
• Type the name of the query
• Click the OK button (Save As dialog box) to save the query

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 12
otherwise on a password-protected website for classroom use.
 Creating Queries (6 of 14)
• To Use a Wildcard
• If necessary, click the Criteria row below the desired field to
produce an insertion point
• If necessary, delete the current entry
• Type the criterion containing the wildcard character (*)

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 13
otherwise on a password-protected website for classroom use.
 Creating Queries (7 of 14)
• To Use Criteria for a Field Not Included in the Results
• With the desired query open, click the Show check box to
remove the check mark for a field containing criteria

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 14
otherwise on a password-protected website for classroom use.
 Creating Queries (8 of 14)
• To Create and View a Parameter Query
• If necessary, return to Design view and type the criterion for a
parameter query ([Enter City], for example)
• Run the query

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 15
otherwise on a password-protected website for classroom use.
 Creating Queries (9 of 14)
• To Use a Parameter Query
• Open the Navigation Pane
• Right-click on the query to produce a shortcut menu
• Click Open on the shortcut menu and display the Enter
Parameter Value dialog box
• Type desired information
• Click OK
• Close the query

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 16
otherwise on a password-protected website for classroom use.
 Creating Queries (10 of 14)
• To Use a Number in a Criterion
• Click the Query Design button to create a new query
• Click the table you wish to add to the query
• Click the Add button to add the selected table to the query
• Click the Close button to remove the dialog box from the
screen
• Add the desired fields to the query
• Add a numeric criterion for a numeric field

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 17
otherwise on a password-protected website for classroom use.
 Creating Queries (11 of 14)
• To Use a Comparison Operator in a Criterion
• Open the query in Design view
• Enter the criterion with a comparison operator

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 18
otherwise on a password-protected website for classroom use.
 Creating Queries (12 of 14)
• To Use a Compound Criterion Involving AND
• Open the query in Design view
• Add the criteria for two fields in the Criteria row

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 19
otherwise on a password-protected website for classroom use.
 Creating Queries (13 of 14)
• To Use a Compound Criterion Involving OR
• Open the query in Design view
• Add criterion for one field to the Criteria row
• Add criterion for another field in the or row (the row below
the Criteria row)

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 20
otherwise on a password-protected website for classroom use.
 Creating Queries (14 of 14)
• Special Criteria
• AND operator
• BETWEEN operator
• IN operator

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 21
otherwise on a password-protected website for classroom use.
 Sorting (1 of 5)
• To Clear the Design Grid
• Open the query in Design view
• Click just above the column heading in the first column in the
grid to select the column
• Hold the SHIFT key down and click just above the last column
heading to select all the columns
• Press the DELETE key to clear the design grid

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 22
otherwise on a password-protected website for classroom use.
 Sorting (2 of 5)
• To Import a Table
• Open the desired table and then click the External Data tab. Both databases
are now open in Access
• In the database you want to import to, Click the “New Data Source” button
• Click From Database in the New Data Source Menu, and then click Access to
display dialog box
• Click the Browse button and navigate to your storage location for the file
• If necessary, click the “Import tables, queries, forms, reports, macros, and
modules into the current database” option button
• Click OK, select the table to import, and click OK
• Close the dialog box
• Close and confirm the table is a table object in the Navigation Pane

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 23
otherwise on a password-protected website for classroom use.
 Sorting (3 of 5)
• To Sort Data in a Query
• Open the query in Design view
• Click the Sort row below the field you wish to sort, and then
click the Sort row arrow to display a menu of possible sort
orders
• Click the desired sort order

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 24
otherwise on a password-protected website for classroom use.
 Sorting (4 of 5)
• To Omit Duplicates
• Open the query in Design view
• Click an empty field in the design grid
• Click the Property Sheet button to display the property sheet
• Click the Unique Values property box, and then click the arrow
that appears to produce a list of available choices
• Click Yes and then close the Query Properties property sheet
by clicking the Property Sheet button a second time

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 25
otherwise on a password-protected website for classroom use.
 Sorting (5 of 5)
• To Sort on Multiple Keys
• Open the query in Design view
• Select a sort order in the Sort column for multiple fields
• To Create a Top-Values Query
• Open the query in Design view
• Click the Return arrow to display the Return menu
• Click an option corresponding to the values you wish to return

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 26
otherwise on a password-protected website for classroom use.
 Joining Tables (1 of 3)
• To Join Tables
• Click the Query Design button to create a new query
• Add two related tables to the new query
• Add the desired fields from each table to the query
• To Change Join Properties
• Open the query in Design view
• Right-click the join line to produce a shortcut menu
• Click Join Properties on the shortcut menu to display the Join
Properties dialog box

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 27
otherwise on a password-protected website for classroom use.
 Joining Tables (2 of 3)
• To Create a Report from a Query
• Open the Navigation Pane, and then select the desired query
in the Navigation Pane
• Click the Report Wizard button to display the Report Wizard
dialog box
• Add the desired fields to the query
• Follow the remaining steps in the wizard to specify a grouping,
sort order, layout and orientation, and title
• Click the Finish button to produce the report

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 28
otherwise on a password-protected website for classroom use.
 Joining Tables (3 of 3)
• To Print a Report
• With the desired report selected in the Navigation Pane, click
FILE on the ribbon to open the Backstage view
• Click the Print Tab in the Backstage view to display the Print
gallery
• Click the Quick Print button to print the report

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 29
otherwise on a password-protected website for classroom use.
 Creating a Form for a Query
• To Create a Form for a Query
• Select the query in the Navigation Pane
• Click the Form button to create a simple form

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 30
otherwise on a password-protected website for classroom use.
 Exporting Data From Access to Other
Applications (1 of 3)
• To Export Data to Excel
• Click the desired query in the Navigation Pane to select it
• Click the Excel button to display the Export - Excel Spreadsheet dialog box
• Click the Browse button (Export - Excel Spreadsheet dialog box) to display the File
Save dialog box
• Navigate to the location to save the exported file
• Click the OK button (Export - Excel Spreadsheet dialog box) to export the data
• Click the “Save export steps” check box (Export - Excel Spreadsheet dialog box) to
display the Save Export Steps options
• Type the desired name for the steps in the Save As text box
• Click the Save Export button (Export - Excel Spreadsheet dialog box) to save the
export steps

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 31
otherwise on a password-protected website for classroom use.
 Exporting Data From Access to Other
Applications (2 of 3)
• To Export Data to Word
• Click the desired query in the Navigation Pane to select it
• Click the More button (External Data tab—Export Group), then click
Word to display the Export-RTF dialog box
• Navigate to the location in which to save the file and assign a file
name
• Click the Save button and then OK to export the data
• Save the export steps, or click Close

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 32
otherwise on a password-protected website for classroom use.
 Exporting Data From Access to Other
Applications (3 of 3)
• To Export Data to a Text File
• Click the desired query in the Navigation Pane to select it
• Click the Text File button (External Data tab—Export Group), then click Export-Tet File dialog
box
• Select the name and location for the file to be created
• If you need to preserve formatting and layout, be sure the “Export data with formatting and
layout” check box is checked
• To create a delimited file, select the Delimited option button and choose your delimiter,
then click Next
• To create a fixed-width file, select the Fixed Width option button, and review the position of
the vertical lines, then click Next
• Click Finish to export the data
• Save the export steps, or click Close

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 33
otherwise on a password-protected website for classroom use.
 Adding Criteria to a Join Query
• To Restrict the Records in a Join
• Open the query containing a join
• Type the criterion for the desired field

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 34
otherwise on a password-protected website for classroom use.
 Calculations (1 of 5)
• To Use a Calculated Field in a Query
• Open the query in Design view containing a field that can be
calculated
• Right-click the Field row in the first open column in the design
grid to display a shortcut menu
• Click Zoom on the shortcut menu to display the Zoom dialog
box
• Type the calculation in the Zoom dialog box
• Click the OK button (Zoom dialog box) to enter the expression

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 35
otherwise on a password-protected website for classroom use.
 Calculations (2 of 5)
• To Change a Caption
• Open the query in Design view
• Click the field in the design grid to which you wish to add the
caption, and then click the Property Sheet button to display
the properties for the field
• Click the Caption box, and then type the desired caption
• Close the property sheet by clicking the Property Sheet button
a second time

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 36
otherwise on a password-protected website for classroom use.
 Calculations (3 of 5)
• To Calculate Statistics
• Create a new query for a table containing fields for which you
can calculate statistics
• Click the Totals button to include the Total row in the design
grid
• Add the field for which you wish to total
• Click the Total row for the added field
• Click the Total arrow to display the Total list
• Select the desired calculation for Access to perform

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 37
otherwise on a password-protected website for classroom use.
 Calculations (4 of 5)
• To Use Criteria in Calculating Statistics
• Add a field to the query containing statistics for which you
wish to add criteria
• Click the Total row in the desired column
• Click the Total arrow for the added field to produce a Total list
• Click Where
• Type the criterion in the Criteria row

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 38
otherwise on a password-protected website for classroom use.
 Calculations (5 of 5)
• To Use Grouping

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 39
otherwise on a password-protected website for classroom use.
 Crosstab Queries (1 of 2)

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 40
otherwise on a password-protected website for classroom use.
 Crosstab Queries (2 of 2)
• To Create a Crosstab Query
• Click the Query Wizard button to display the New Query dialog
box
• Click Crosstab Query Wizard (New Query dialog box)
• Click the OK button to display the Crosstab Query Wizard
dialog box
• Follow the instructions in the wizard to select the row and
column headings for the query, and then name the query

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 41
otherwise on a password-protected website for classroom use.
 Customizing the Navigation Pane
• To Customize the Navigation Pane
• If necessary, click the Shutter Bar Open/Close Button to open
the Navigation Pane
• Click the Navigation Pane arrow to produce the Navigation
Pane menu
• Click the desired option to organize the Navigation Pane

© 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or
in part, except for use as permitted in a license distributed with a certain product or service or 42
otherwise on a password-protected website for classroom use.