Lesson 33: Creating Complex Queries

1 / 12

# Lesson 33: Creating Complex Queries - PowerPoint PPT Presentation

Lesson 33: Creating Complex Queries. 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Lesson 33: Creating Complex Queries' - fonda

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Lesson 33: Creating Complex Queries

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

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]
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 Ribbon

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