copyright © 2008 pearson prentice hall. all rights reserved. 1 11 committed to shaping the next...
TRANSCRIPT
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 1Copyright © 2008 Pearson Prentice Hall. All rights reserved. 11
Committed to Shaping the Next Generation of IT Experts.
Chapter 7
Advanced QueriesRobert Grauer, Keith Mulbery, Maurie Wigman Lockley
Exploring Microsoft Office Access 2007
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 2
Objectives
Maintain a database Understand action queries Create an Update query Use a make table query Create a delete query
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 3
Objectives
Construct a crosstab query Work with parameter queries Create find unmatched queries Understand find duplicate queries
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 4
Action Queries
Change underlying table data Select queried do not
Can be used to create new tables Cannot be undone Play a large role in maintaining a database
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 5
About Update Queries
Changes value(s) in one/ more records
Use the Update query button Database Tools tab,
Query Type group
Update button
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 6
Creating an Update query
Identify record source Show desired tables in
design grid Switch from Select to
Update Query Insert Field to be
updated Enter the value to be
updated Value entered in Update
To field
Update value
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 7
Testing Action Queries
Test all action queries before execution Choose Datasheet View
Allows preview of results before execution
Check Datasheet View first
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 8
Running an Update Query
Click the Run button Design tab, Results group
View table to see the results
Run button
Before Update query
After Update query
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 9
Specifying Criteria in an Action Query
Specified in the field being update Also specified using an additional field(s)
Criteria
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 10
Using Criteria with an Update Query
Specifies specific records for update Works the same as in a select query
After update query with criteria
Before update query with criteria
Criteria
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 11Copyright © 2008 Pearson Prentice Hall. All rights reserved. 11
About Append Queries
Adds records from one table to another Data types between tables must match Validation rules in existing table apply
Records not added if validation rule violated Source table fields may be ignored
Do not add to design grid If auto-number fields exists in target table
Do not import auto-number fields
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 12
Creating an Append Query
Identify target and source tables Click Append button Choose Target table from Append box
May also browse to choose another database
Append dialog box
Append button
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 13
Creating an Append Query
Add fields from Source table to design grid Test in Datasheet view Run query
Fields to append added to design grid
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 14
Append Query Results
Data is appended to existing table
February Agents before append
September Agents before and after append
February Agents after append
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 15
About a Make Table Query
Almost the same as append query Table must preexist in append
query No pre-existing table in make
table query Target database must exist
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 16
Creating a Make Table Query
Identify the source table Click Make Table button Enter table name or choose database
Accomplished in the Make table dialog box
Make Table dialog box
Make Table button
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 17
Running a Make Table Query
Add fields to design grid Test in Datasheet view Run query
Fields added in design grid
Test in Datasheet view
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 18
Make Table Query Results
New table added to Objects pane Open table to view results
Source Table
Results of make table query
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 19
About Delete Queries
Can delete all records from table Criteria used to delete specific records If no criteria, all records deleted
Very powerful – cannot be undone Setup is same as other action queries
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 20
Creating a Delete Query
Add table to Design grid Click Delete Button Add field(s) to design grid
Use criteria to specify delete conditions
Add table to design grid
Delete button
Delete Query with criteria
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 2121
Running a Delete Query
Test query in Datasheet view Run Query Open table to view results
Test in Design view
Table before delete query
Table after delete query
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 22
Hiding a Query
Prevents accidental execution of action queries Right-click query Click the Hidden checkbox
All queries shown
Some queries hidden
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 2323
About Crosstab Queries
Summarizes by two sets of unrelated facts Output resembles a PivotTable
PivotTable primarily for management decision making
Crosstab query primarily for end users Will not work on related data Works with one or more tables
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 2424
Crosstab Query – Using the Query Wizard Step 1
Click the Query Wizard button In the New Query dialog box
Select Crosstab Query Wizard
Select Crosstab Query Wizard
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 2525
Using the Query Wizard Step 2
Select table/query to use in crosstab To use fields from multiple tables
Create query from tables first
Select source table or query to use
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 2626
Using the Query Wizard Step 3
Select fields(s) for column headings
Select field(s) for column headings
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 2727
Using the Query Wizard Step 4
Select the field to calculate Select the appropriate aggregate function
Select aggregate function
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 28
Using the Query Wizard Step 5
Enter new query name or accept default View or modify the query
Enter new name or accept default name
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 29
Completed Crosstab Query
Summarizes list price Per selling/listing agent per city
Source table
Crosstab query
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 30
About Parameter Queries
Information intentionally omitted by designer Allows end user to supply criteria Customizable data request
More flexible than other query types Considered a select query Designers able to lock query design
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 31
Creating a Parameter Query
Identify tables/ fields in design view Create prompt for user in criteria field
Prompt must be enclosed in brackets [ ] Multiple fields can be used as prompts
Message for user entered in brackets
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 32
Running a Parameter Query
Click the Run button Enter criteria in message box
Criteria rules and use is same as normal select query View results in Datasheet view
Message prompt with criteria entered
Parameter query results
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 33
Parameter Report
Normally based of a parameter query If not based on parameter query
Open reports source query in design view Add parameter
OR View report in Design Open property sheet for field Click Record Source property - add parameter
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 34
About Unmatched Queries
Compares records in two related tables Returns records found in only one table Query Wizard easiest method of creation
Unmatched Query results
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 35
Unmatched Query Wizard
Run Query Wizard Select Find Unmatched Query Wizard
Find Unmatched Query
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 36
Unmatched Query Wizard
Select first table source
Select second table source
Select first table source
Select second table source
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 37
Unmatched Query Wizard
Select the duplicate fields Click the <=> button
Select display fields for query results
Click Finish
Select duplicate fields
Select fields for query results
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 38
About Find Duplicates Query
Finds duplicate data Aids in data entry error detection Allows administrative use of purposely duplicated data Easily created with the Query Wizard
Query Wizard
Find Duplicates query result
Copyright © 2008 Pearson Prentice Hall. All rights reserved. 39Copyright © 2008 Pearson Prentice Hall. All rights reserved. 39