Lesson 33: Creating Complex Queries

2

Lesson Objectives After studying this lesson, you will be able to:

Create a select query involving multiple tables Create a report based on multiple tables in a query

Create and run parameter queries Create a calculated field in a query Create and run action queries Analyze, compact, repair, backup, and restore a

database

Access Query Types Select Query Crosstab Query Unmatched Query Duplicates Query Parameter Query Action Query SQL Query

3

Working with Relationships

4

When relationships are missing, querying a database using fields from multiple tables creates unexpected, meaningless results

These results are called Cartesian products Each record is listed multiple times

Parameter Queries Prompt users for input to narrow the query output Criteria expression contains prompt text

Criteria expression appears in the query grid Criteria row

Criteria expression prompts user for a value when the query is run

Tip! Brackets always surround the criteria expression.

5

Working with Calculated Controls Calculated fields can be used to combine text strings

Last Name: Brown First Name: Samuel Name: [First Name] + [Last Name]=Samuel Brown

Calculated fields are constructed as follows: New Field Name: [Existing Field Name] Operation

[Existing Field Name]

6

Action Queries Delete Query

Deletes a group of records from one or more tables Update Query

Makes global changes to a group of records in one or more tables

Append Query Adds a group of records from one or more tables to the

end of one or more tables Make-Table Query

Creates a new table from all or selected data in one or more tables

Setting Up an Action Query Each action query has a source and destination table Action types are set using Query Type controls on the

RibbonAction query types

Source table is the active table; destination table identified in the dialog box

Analyzing and Documenting Databases Performance Analyzer

Reviews structures of database objects Identifies potential trouble spots/objects Reports the results of the analysis Ensures smooth functioning of the database

Database Documenter Reviews all database objects Documents all objects and their associations Includes field names and other significant

information

10

Backing Up & Compacting Databases Backing up a database

Makes a duplicate of the database for easy retrieval Adds the data to the filename each time you back up

Compacting and repairing a database Optimizes database performance Identifies problems with a database that could corrupt it

and attempts to fix the problem No new file is created when you compact and repair

Adding Totals to Datasheets Enables you total any datasheet column Total appears at the bottom of the datasheet column Functions such as Sum, Min, Max, and Avg are

available for totaling columns

11

Lesson 33: Creating Complex Queries