Summarize the three most important things you have learned in this PowerPoint.
papi2lit
M02_Access2016.pptxShelly Cashman: Microsoft Access 2016
Module 2: Querying a Database
© 2017 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.
‹#›
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
© 2017 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.
‹#›
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
© 2017 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.
‹#›
Project-Querying a Database
Roadmap
Create queries in Design view
Use criteria in queries
Sort data in queries
Join tables in queries
Export query results
Perform calculations in queries
Create a crosstab query
Customize the Navigation Pane
© 2017 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.
‹#›
Creating Queries (1 of 10)
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
© 2017 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.
‹#›
Creating Queries (2 of 10)
© 2017 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.
‹#›
Figure 2-2: display of a query in Design view
6
Creating Queries (3 of 10)
To Add Fields to the Design Grid
Double-click each field to add to the query
© 2017 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.
‹#›
Figure 2-5: display of the fields in the design grid
7
Creating Queries (4 of 10)
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
© 2017 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.
‹#›
Figure 2-6: display of the Run button
8
Creating Queries (5 of 10)
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 (*)
© 2017 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.
‹#›
Creating Queries (6 of 10)
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
© 2017 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.
‹#›
Figure 2-16: display of Show Check box
10
Creating Queries (7 of 10)
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
© 2017 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.
‹#›
Figure 2-18: display of parameter query
11
Creating Queries (8 of 10)
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
© 2017 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.
‹#›
Creating Queries (9 of 10)
To Use a Comparison Operator in a Criterion
Open the query in Design view
Enter the criterion with a comparison operator
© 2017 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.
‹#›
Figure 2-25: display of comparison operator
13
Creating Queries (10 of 10)
To Use a Compound Criterion Involving AND
Open the query in Design view
Add the criteria for two fields in the Criteria row
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)
© 2017 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.
‹#›
Sorting (1 of 4)
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
© 2017 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.
‹#›
Sorting (2 of 4)
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
© 2017 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.
‹#›
Sorting (3 of 4)
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
© 2017 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.
‹#›
Sorting (4 of 4)
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
© 2017 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.
‹#›
Joining Tables (1 of 2)
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
© 2017 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.
‹#›
Joining Tables (2 of 2)
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
© 2017 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.
‹#›
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
© 2017 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.
‹#›
Figure 2-55: display of fields in Manager-Account Query
21
Exporting Data From Access to Other Applications
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
If necessary, type a description for the export steps
Click the Save Export button (Export - Excel Spreadsheet dialog box) to save the export steps
© 2017 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.
‹#›
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
© 2017 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.
‹#›
Figure 2-61: display of the criterion for Amount Paid field
23
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
© 2017 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.
‹#›
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
© 2017 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.
‹#›
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
© 2017 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.
‹#›
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
© 2017 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.
‹#›
Calculations (5 of 5)
To Use Grouping
© 2017 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.
‹#›
Figure 2-76: display of group by Account Manager Number
28
Crosstab Queries (1 of 2)
© 2017 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.
‹#›
Figure 2-78: display of a cross tab query
29
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
© 2017 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.
‹#›
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
© 2017 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.
‹#›
