lesson 33 creating complex queries n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Lesson 33: Creating Complex Queries PowerPoint Presentation
Download Presentation
Lesson 33: Creating Complex Queries

Loading in 2 Seconds...

play fullscreen
1 / 12

Lesson 33: Creating Complex Queries - PowerPoint PPT Presentation


  • 83 Views
  • Uploaded on

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

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

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


Download Now An Image/Link below is provided (as is) to download presentation

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 objectives
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
Access Query Types
  • Select Query
  • Crosstab Query
  • Unmatched Query
  • Duplicates Query
  • Parameter Query
  • Action Query
  • SQL Query
working with relationships
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
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
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
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
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
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 & 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
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