1. 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

3. Access Query Types • Select Query • Crosstab Query • Unmatched Query • Duplicates Query • Parameter Query • Action Query • SQL Query

4. Working with Relationships • 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

5. Parameter Queries • Prompt users for input to narrow the query output • Criteria expression contains prompt text Criteria expression prompts user for a value when the query is run Criteria expression appears in the query grid Criteria row Tip! Brackets always surround the criteria expression.

6. 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]

7. 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

8. Setting Up an Action Query • Each action query has a source and destination table • Action types are set using Query Type controls on the Ribbon Action query types Source table is the active table; destination table identified in the dialog box

9. 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

11. 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

12. Lesson 33: Creating Complex Queries