Transcript Web-Enabled Decision Support Systems
Web-Enabled Decision Support Systems Queries: Building Application Foundation
Prof. Name Position University Name 1 [email protected]
(123) 456-7890
Overview
8.1 Introduction
8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields 8.8 Parameter Queries 8.9 Crosstab Queries 8.10 Action Queries 8.11 Update Queries 8.12 Delete Queries 8.13 Append Queries 8.14 In-Class Assignment 8.15 Summary 2
Introduction
A database
query
is a question posed against database tables – A set of instructions – Enable us to present a
unified view of data
From single or multiple tables – When executed, operate on database tables to output the
query result
Single Number (aggregate query) Record Set (select query) Built using: – Graphical query design grid (Query By Example, QBE) – Standard Query Language (SQL) statements 3
Introduction
(Cont.) Access queries can be broadly classified into three function-based categories: – Select queries – Special purpose queries (Crosstab, Parameter, and Make-Table queries) – Action queries (Update, Append, and Delete queries) The Access Query Menu 4
Overview
8.1 Introduction
8.2 Working with Select Queries
8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields 8.8 Parameter Queries 8.9 Crosstab Queries 8.10 Action Queries 8.11 Update Queries 8.12 Delete Queries 8.13 Append Queries 8.14 In-Class Assignment 8.15 Summary 5
Working With Select Queries
Select Queries – Most common type of query used in database applications – Extract and output recordsets that satisfy specific selection criteria: Arithmetic expressions Logical expressions – Composed of three types of operations: Projection operations Selection operations Sorting operations 6
Projection
Projection
is a vertical slicing of an input recordset – Used to display one or more selected fields of a table – Example: Input recordset:
Student
table Output recordset:
Name
,
, and
DOB
fields The Projection Operation 7
Selection
Selection
is a horizontal slicing of an input recordset – Used to select one or more records (rows) that satisfy specified selection criteria – Example: Input recordset:
Student
table Selection criteria:
Type
= “Full Time” The Selection Operation 8
Sorting
Sorting
provides an ordered sequence of an input recordset – Example: Input recordset:
Student
table Sort:
StudentID
(ascending) The Sorting Operation 9
Creating a Query in the Design View
The
Design View
and
Simple Query Wizard
are the two most frequently used options for creating queries How-to: Create a New Query Using Design View 1. Select the
Queries
option in the Objects Bar to display the two popular options for creating a new query.
“Create Query” Options 10
Creating a Query in the Design View
(cont.) 2. Double-click on the
Create query in Design View
option to open a new query in the Design View.
New Query
Dialog Box 11
Query Design View
The
Query Design View
is divided into two halves: – The upper half, called a
Table Pane
, displays all the fields from the desired query tables – The lower half, the
Design Grid
, holds the table fields on which we would like perform Projection, Selection, and/or Sorting operations The Query Design View 12
A Select Query Example
Query: – “List the students’ information (
StudentID
,
Name
, and
) for all full time, freshman students sorted in descending order of their names.” Steps: 1. Projecting student identification number, name, and email columns 2. Selecting only full time, freshman students 3. Sorting selected records in descending order of student names 13
Projecting Student’s Contact Information
How-to: Build a Select Query 1. Open the university database and create a new query in the Design View.
2. Select the table
tblStudent
design. and click the
Add
button to add it to the query 3. Click
Close
to exit the
Show Table
dialog box. 4. Add the
StudentID
,
Name
, and
fields to the Design Grid. Projecting Fields of the Student Table 14
Saving and Executing a Query
How-to: Build a Select Query (cont.) 5.
Save this query as “QrySelection” and run the query to verify the Projection operation. To save, choose
File
|
Save
from the Main menu.
To run, choose
Query
|
Run
from the Main menu.
15
Selecting Freshman and Fulltime Students
How-to: Build a Select Query (cont.) 6.
Add =“Freshman” criterion for the
Class
column and =“Full Time” criterion for the
Type
column in the
Criteria
row.
7. Uncheck the check boxes in the
Show
columns. row for the
Class
and
Type
8. Save and run the query to verify Selection operation.
Selecting Rows of the Student Table 16
Sorting Student’s Records
By default, query results are displayed in the same order in which they appear in the table How-to: Build a Select Query (cont.) 9.
Choose the “Descending” order using the drop-down list in the
Sort
row of the
Name
column.
Sorting Student Records in Descending Order by Student Names 17
Query Output
How-to: Build a Select Query (cont.) 10. Save and run the query to verify Selection operation.
Output of the Example Select Query 18
Moving or Removing Fields and Tables in Design View
By default, the order of the columns in the Design Grid is the order of the columns in the query result Moving columns in the Design Grid: – Select the entire column and drag it to the desired location. Removing columns from the Design Grid: – Select the entire column and hit the
Delete
button.
Removing tables from the Table Pane: – Select the table by clicking on the table list and then remove it by hitting the
Delete
button.
– Removes the corresponding columns of that table from the Design Grid. 19
Exploring Query Toolbar and Menus
Picture Name
Design View Query Type Show Table
Functionality
Switches to the Design View Provides options to choose query type Opens the
Show Table
dialog box to add tables/queries
Main Menu
View | Design View Query | {Query Type Name} Query | Show Table Totals Properties Adds
Total
row to the Design Grid for total queries View | Totals Opens the
Query Properties
window View | Properties Build Opens the
Expression Builder
dialog box -
Short-cut Menu
Table Pane | Design View Table Pane | Query Type | {Query Type Name} Table Pane | Show Table
Criteria
- Design Grid | Properties row | Build 20
Overview
8.1 Introduction 8.2 Working with Select Queries
8.3 Queries with Multiple Tables (The Join Operation)
8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields 8.8 Parameter Queries 8.9 Crosstab Queries 8.10 Action Queries 8.11 Update Queries 8.12 Delete Queries 8.13 Append Queries 8.14 In-Class Assignment 8.15 Summary 21
Queries with Multiple Tables (The Join Operation)
Queries can systematically join multiple tables to create a unified view of the fragmented data
Join operations
involve adding multiple tables to the Table Pane – Combine with projection, selection, and sorting operations to create more complex queries The Join Operation 22
Hands-On Tutorial: Working with Join Queries
Query: – “List the student information (
StudentID
departments (not the
DeptID
).” and
Name
) and name of their How-to: Design a Query that Involves Multiple Tables 1. Open the university database and start with a new query in the Design View. Add the
tblStudent
and
tblDepartment
to the Table Pane.
2. Add the required fields to the Design Grid. Joining Student and Department Tables in the Design View 23
Hands-On Tutorial: Working with Join Queries
(cont.) 3.
Save the query as “QryJoinQuery” and run it. The Join Result in the Datasheet View 24
The Join Operation
The
join operation
takes two distinct recordsets as an input and produces a single output recordset, referred as the
join result
– Each record from the first recordset is joined with all records in the second – The join result filters these joined records via the
join condition
An
inner join
is based on an equality condition of primary key-foreign key columns – Default join type – Join results will include records from both the tables which satisfy the primary key-foreign key conditions – When adding related tables to the Table Pane, Access automatically displays such relationships 25
The Join Operation
(cont.)
Outer joins
include all the records from one table and only those records from the other table where joined fields are equal –
Left outer joins
include all records in the left table Default outer join type –
Right outer joins
include all records in the right table If the two tables added to the Table Pane to join are not related to each other, no join condition is used in the join operation – Join result in an
all-to-all join
(or
Cartesian product join
) – Each record from the first input recordset is joined with all the records from the second input recordset 26
Join Properties
We can view and alter join types using the
Join Properties
dialog box – To invoke the
Join Properties
dialog box, right-click the relationship line in the Table Pane and choose
Properties
from the short-cut menu. Selecting a Join Type in
Join Properties
Dialog Box 27
Overview
8.1 Introduction 8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation)
8.4 Working with Operators
8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields 8.8 Parameter Queries 8.9 Crosstab Queries 8.10 Action Queries 8.11 Update Queries 8.12 Delete Queries 8.13 Append Queries 8.14 In-Class Assignment 8.15 Summary 28
Working with Operators
In Access, we have an option to use different operators in the query design to build conditional statements Most commonly used operators: – Comparison operators – Logical operators – BETWEEN operator – LIKE operator 29
Comparison Operators
We use
comparison operators
to select a specific group of records from a table – Operators: >, >=, <, <=, =, != Examples: –
CreditHours
> 3.0
–
Year
= 2002 30
The Logical Conditions
We need
logical operators
to combine multiple criteria – Operators: AND, OR, and NOT Examples: – – –
Salary
> 7000 AND
Salary
< 10000
Class
= “Sophomore” OR
Class
= “Freshman”
Class
= “Sophomore” AND
Type
= “Full Time” 31
The BETWEEN Operator
We use the
BETWEEN operator
along with the logical AND operator to specify an inclusive range between two values – For use with text, numeric, or date fields Examples: – BETWEEN #1/1/95# AND #1/1/2002# – BETWEEN 70,000 AND 100,000 32
The LIKE Operator and Wildcard Character
The
LIKE operator
finds groups of potentially related items It is often used with the
wildcard character
, the asterisk (*) Examples: – LIKE “*Computer*” Text includes “Computer” – LIKE “Applied*” Text begins with “Applied” 33
Working with Dates
We use the pound sign (#) to represent the date data type Access has many built-in functions to handle dates and times data types Example: – <= #1/1/2001# Everything on and before January 1, 2001 – < Now() Everything before current date 34
Overview
8.1 Introduction 8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators
8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables
8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields 8.8 Parameter Queries 8.9 Crosstab Queries 8.10 Action Queries 8.11 Update Queries 8.12 Delete Queries 8.13 Append Queries 8.14 In-Class Assignment 8.15 Summary 35
Hands-On Tutorial: A Select Query with Join of Multiple Tables
Query: – “List the contact information (
name
,
, and
address
) for all Industrial Engineering (ISE) students who have taken any computer- or database related courses in the last six years and earned a grade point average of 3.5 or better in these courses. Sort the list in descending order of student’s letter grade.” Tables: – Student table – Transcript table – Section table – Course table 36
Hands-On Tutorial
(cont.)
: A Select Query with Join of Multiple Tables
How-to: Write a Complex Query using the Projection, Selection, Sorting, and Join Operations 1. Open the university database and start with a new query in the Design View. Add the required four tables to the Table Pane. 2. Add the required fields from their respective tables to the Design Grid. A Select Query Design with Multiple Tables 37
Hands-On Tutorial
(cont.)
: A Select Query with Join of Multiple Tables
3. Add the criteria >= 3.5 for the
Grade
=“ISE” for the
DeptID
field. column. Specify the expression Save and run the query to verify the design so far.
4. Assign the expression > Year (Now ()) - 6 in the
Year
field. A Select Query Design with Multiple Tables 38
Hands-On Tutorial
(cont.)
: A Select Query with Join of Multiple Tables
5.
Place the final expression Like “*computer*” Or Like “*database*” in the course name field. 6. Use the
Sort
row to specify descending order for the
Grade
column. 7. Use the
Show
row to display only the
Name
,
Address
, and
columns. Uncheck all other columns in the
Show
row.
8. Save and run the query. 39
Overview
8.1 Introduction 8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables
8.6 Using Total Row (The GroupBy Operation)
8.7 Creating Calculated Fields 8.8 Parameter Queries 8.9 Crosstab Queries 8.10 Action Queries 8.11 Update Queries 8.12 Delete Queries 8.13 Append Queries 8.14 In-Class Assignment 8.15 Summary 40
Using Total Row (The GroupBy Operation)
The
GroupBy operation
puts together records based on some similarity criteria and treats them as one unit or a group – Extensively used to furnish data summaries – Allows us to apply aggregate functions to the set of records 41
Hands-On Tutorial: Using the Total Row
Query: – “Determine the total number of students in each department.” How-to: Create a
Totals
(or GroupBy) Query 1. Open the university database and start with a new query in the Design View. Add the student and department tables. 2. Add the student ID and department name fields to the Design Grid. 3. Designate this query as a
Totals
the toolbar. query by clicking on the
Totals
button on 4. Click in the
Total
row under the
StudentID
column, and select the
Count
option from the drop-down list of aggregate functions.
42
Hands-On Tutorial: Using the Total Row
(cont.) 5. The Design View should appear as below: Grouping Student Records by Department in the Design View 43
Hands-On Tutorial: Using the Total Row
(cont.) 6. Save and run the query for the result shown below.
Aggregation fields (like
StudentID
in this example) are named as
7. Rename the
StudentID
count column as “
TotalStudents: StudentID
”.
The Output of GroupBy Operation 44
Hands-On Tutorial: Using the Total Row with the Where Clause
Query: – “List the course information for those courses whose average evaluation was better than 80 points in the last 5 years.” How-to: Create a
Total
Query Using the
Where
Clause in the
Total
Row 1. Open the university database and start with a new query in the Design View. Add the course and section tables. 2. Add the
Name
field from the course table and the fields from the section table to the Design Grid.
Evaluation
and
Year
3. Designate this query as a
Total
query by clicking on the
Totals
the toolbar. button on 45
Hands-On Tutorial
(cont.)
: Using the Total Row with the Where Clause
4. Click on the
Total
row under the
Evaluation
column, and select the
Average
option from the drop-down list of aggregate functions. 5. Add selection criteria (> Year (Now ()) - 5) using the
Where
option under the
Year
column. 6. Specify the selection criteria (>80) for the
Evaluation
column. 7. Remove the check in the 8.
Show
row for the
Year
column. Save the query as “QryCourseEvaluation” and run it. The Query Output 46
Hands-On Tutorial
(cont.)
: Using the Total Row with the Where Clause
– The final Design View is shown below:
The Total Row with the Where Clause
47
Overview
8.1 Introduction 8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation)
8.7 Creating Calculated Fields
8.8 Parameter Queries 8.9 Crosstab Queries 8.10 Action Queries 8.11 Update Queries 8.12 Delete Queries 8.13 Append Queries 8.14 In-Class Assignment 8.15 Summary 48
Creating Calculated Fields
Access queries allow us to create
calculated fields
as a part of the query design – Used to represent derived attributes Attributes that are calculated using other attributes of the relation – Expressed as a new field in separate column of the query Design Grid Instead of adding a column from the Table Pane, we write an expression in the Design Grid that, when evaluated, gives the value of the field Existing field names are enclosed in square brackets [ ] to distinguish them from other entries in the expression – Example: Age: Now() – [DateOfBirth] 49
Hands-On Tutorial: Creating Calculated Fields
Calculated Field: – Grade Point Average (GPA) Calculated by first summing the product of the grade point and corresponding credit hours and then averaging it over the total number of credit hours earned How-to: Create the Calculated Fields in Access Queries 1. Open the university database and start with a new query in the Design View. Add required tables to the Table Pane.
2. Add the student
Name
,
StudentID
, and
CreditHours
Grid. fields to the Design 3. Click the
Total
function in the button on the toolbar and select the
Total
row of the
CreditHours Sum
aggregate column. Rename this column as
TC:CreditHours.
50
Hands-On Tutorial: Creating Calculated Fields
(cont.) 4. Add the calculated field TG: [CreditHours]*[Grade]. Choose the
Sum
aggregate in the
Total
row.
5. Add another calculated field, GPA: [TG]/ [TC]. Assign an
Expression
option in the
Total
row for this column.
Creating Calculated Fields for GPA Calculations 51
Calculated Fields with Strings
We can also perform string operations on fields Example: – Name: [FirstName] & “ ” & [LastName] Obtains the full name by combining the first name and the last name The ampersand (&) operator combines the string values from
FirstName
,
LastName
, and an empty string 52
Expression Builder
The Access
Expression Builder
dialog box simplifies the process of developing complicated expressions for calculated fields – To invoke the
Expression Builder
dialog box, click the
Build
button on the toolbar.
The Expression Builder Dialog Box
53
Overview
8.1 Introduction 8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields
8.8 Parameter Queries
8.9 Crosstab Queries 8.10 Action Queries 8.11 Update Queries 8.12 Delete Queries 8.13 Append Queries 8.14 In-Class Assignment 8.15 Summary 54
Parameter Queries
A
Parameter query
is a query that prompts for parameter values and dynamically assigns user-entered values in the selection criteria to run the query – At each execution, users may enter different parameter values for the desired result We create a Parameter query in the same manner we create a Select query – We specify one or more parameters of the query in the selection criteria using square brackets [ ] DeptID = [ ] – To prompt the user with an appropriate message to enter parameter values, enclose the message in the square brackets DeptID = [Please Enter Department Name] 55
Parameter Queries
(cont.) The Parameter Query in the Design View 56 The Query Output
Hands-On Tutorial: Working with Parameter Queries
Query: – “Display a list of faculty members who have joined a particular college within a specified date range.” Tables: – – –
tblCollege tblDepartment tblFaculty
57
Hands-On Tutorial: Working with Parameter Queries
(cont.) How-to: Work with the Parameter Queries 1. Open the university database for a new query in the Design View. Add the college, department, and faculty tables to the Table Pane.
2. Add fields to the Design Grid as shown below. The Parameter Query with Multiple Parameters 58
Hands-On Tutorial: Working with Parameter Queries
(cont.) 3.
Enter the “[Enter the College]” parameter for the
CollegeID
column. Also, assign the “Between [Starting Date] And [Ending Date]” criteria under the
JoiningDate
column. 4. Save and run the query. The Parameter Query with Multiple Parameters 59
Overview
8.1 Introduction 8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields 8.8 Parameter Queries
8.9 Crosstab Queries
8.10 Action Queries 8.11 Update Queries 8.12 Delete Queries 8.13 Append Queries 8.14 In-Class Assignment 8.15 Summary 60
Crosstab Queries
Crosstab queries
are used to group and summarize the information and display it in a spreadsheet format – Less repetition of information than in the Datasheet View – Easier to read and analyze Crosstab queries are defined by three fields: – Row-heading fields:
DeptID
– Column-heading fields:
Class
(one column for each distinct Class) – Value fields:
StudentID
Output of a Crosstab Query 61
Hands-On Tutorial: Working with Crosstab Queries
Query: – “For all the courses in the Computer Science department, we would like to display the total student enrollment in the Fall, Spring, and Summer semesters.” Fields: – Row-heading fields:
Course Names
– Column-heading fields:
Sem
– Value fields:
StudentID
62
Hands-On Tutorial: Working with Crosstab Queries
(cont.) How-to: Work with Crosstab Queries 1. Open the university database with the new query, and add the course, section, and transcript tables to the Table Pane.
2. Add the
CourseID
,
Name
, and
DeptID
fields from the course table, the
StudentID
field from the transcript table, and the section table.
Sem
field from the 3. Change the type of query from the default Select query to the Crosstab query by choosing the
Query
|
Crosstab Query
item from the Main menu. 4. Select the
Row Heading
option for the
CourseID
and
Name
columns in the
Crosstab
row. 63
Hands-On Tutorial: Working with Crosstab Queries
(cont.) 5. Select the
Column Heading
option for the
Sem
field. 6. Select the
Value
option for the
StudentID
function in the
Total
row for the
StudentID
field. Also choose the
Count
column. 7.
Specify the department selection criteria (=“CISE”) under
DeptID
column. The Crosstab Query (Students per Semester per Course) in the Design View 64
Hands-On Tutorial: Working with Crosstab Queries
(cont.) 8. Save and then run the query to produce the result shown: The Output of the Crosstab Query (Students per Semester per Course) 65
Overview
8.1 Introduction 8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields 8.8 Parameter Queries 8.9 Crosstab Queries
8.10 Action Queries
8.11 Update Queries 8.12 Delete Queries 8.13 Append Queries 8.14 In-Class Assignment 8.15 Summary 66
Action Queries
Queries that can alter data by performing actions are called
action queries
– Allow us to modify a large number of records without having to write any special programs – Does not return a recordset – Three Types:
Update queries
modify the values of one or more fields in the table
Delete queries
use criteria to delete one or more records from the table
Append queries
append one or more records to an existing table 67
Overview
8.1 Introduction 8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields 8.8 Parameter Queries 8.9 Crosstab Queries 8.10 Action Queries
8.11 Update Queries
8.12 Delete Queries 8.13 Append Queries 8.14 In-Class Assignment 8.15 Summary 68
Update Queries
An
update query
is an action query that can change field values in an existing table – Primarily update existing values in the database fields – Example: Increase the
Salary
field of the faculty table by 5% Faculty Table Before 69 Faculty Table After
Update Queries
(cont.) We can select the records we would like to update by specifying update criteria – Example: “Raise the salary for faculty members who joined the university before 1995 and have a salary of less than $70,000.” The Update Query in the Design View 70
Hands-On Tutorial: Working with an Update Query
Query: – “Update the
Salary
field in the faculty table for those faculty members who joined the university before 1995 and have a salary less than $70,000.” How-to: Work with Update Queries 1. Open the university database and begin with a new query in the Design View. Add the faculty table to the query design.
2. Change the query type to the Update query. 3. Add the
Salary
and
JoiningDate
Grid. In the
Salary
fields from the table list to the Design field, enter the expression [Salary]*1.05 under the
Update To
row.
71
Hands-On Tutorial: Working with an Update Query
(cont.) 4. In the
JoiningDate
the
Salary
field, specify the following criteria: “< #1/1/1995#”. In field, enter the criteria: “< 70,000”.
5.
Save the query as “QrySalaryUpdates” and run it using the
Run
button on the toolbar. Click
Yes
when Access flashes a warning message.
Access’ Update Warning 72
How to Preview Records that the Action Query Alters
When we run action queries, the changes made to the database tables are irreversible – It is important that we preview the exact changes that will occur To preview, click the
View
button on the toolbar at the top left corner of the Database Window. This opens a Datasheet View with records that the action query will alter. Preview of an Update Query in the Datasheet View 73
How Cascade Updates Work with Update Queries
What happens when we update the primary key of a table that is a foreign key in some other table and vice versa? – Update primary key: Access allows us to update the primary key value If the
Cascade Updates
option is selected, then Access automatically updates the foreign key value to maintain referential integrity – Update foreign key: Access warns us for referential integrality constraint violations If we continue with this update, we leave data in an inconsistent state The Cascade Update Error 74
Overview
8.1 Introduction 8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields 8.8 Parameter Queries 8.9 Crosstab Queries 8.10 Action Queries 8.11 Update Queries
8.12 Delete Queries
8.13 Append Queries 8.14 In-Class Assignment 8.15 Summary 75
Delete Queries
A
delete query
is an action query that deletes records from existing tables – Allow us to delete specific groups of records by applying deletion criteria to the appropriate fields The Delete Query in the Design View 76 Preview of the Delete Query Displaying Records to be Deleted
Hands-On Tutorial: Building a Delete Query
Query: – “Delete the Industrial Engineering student records for those students who have recently graduated (i.e., say all of the senior class).” How-to: Work with Delete Queries 1. Open the university database and start with the new query in the Design View. Add the student and department tables to the query design.
2. Change the query type from the default Select query to the Delete query. 3. Add the
Class
and
DeptID
fields from the student and department lists, respectively, to the Design Grid. 77
Hands-On Tutorial: Building a Delete Query
(cont.) 4. Add all fields from the student tables by adding an asterisk (*) to the Design Grid. 5. Specify the criteria fields for
Class
as = “Senior” and for
DeptID
as = “ISE”. 6.
Save the query as “qryDelStudents”. Preview the deleted records using the
View
button on the toolbar.
The Delete Query in the Design View 78
Caution with Cascade Delete Option
Access performs cascade deletes when the delete query removes records that contain primary key values – Cascade deletes often increase the danger of massive data loss – Should be used with caution 79
Append Queries
8.1 Introduction 8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields 8.8 Parameter Queries 8.9 Crosstab Queries 8.10 Action Queries 8.11 Update Queries 8.12 Delete Queries
8.13 Append Queries
8.14 In-Class Assignment 8.15 Summary 80
Append Queries
An
append query
is an action query that copies records from one or more tables and adds them at the end of another table – Example: “Retrieve the contact information and the date of birth of students in the College of Engineering and append them to a temporary table in the database for further processing.” Append Query in the Design View 81 Preview of the Append Query
Hands-On Tutorial: Building an Append Query
Query: – “Select the contact information and birthdates for all students in the College of Engineering and append them to a temporary table,
tblStudentArchive
, in the database for further processing.” How-to: Work with Append Queries 1. Start with a new query and add the student, department, and college tables to the query design. 2. Change the query type to the Append query to display the
Append
dialog box. Select the
tblStudentArchive
table from the drop-down list. The
Append
Dialog Box 82
Hands-On Tutorial: Building an Append Query
(cont.) 3. Add all the required fields and the selection criteria as shown below. 4.
Save the query as “QryStudentArchive”. Preview the records selected to append to the archive table and run the query. Append Query in the Design View 83
Overview
8.1 Introduction 8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields 8.8 Parameter Queries 8.9 Crosstab Queries 8.10 Action Queries 8.11 Update Queries 8.12 Delete Queries 8.13 Append Queries
8.14 In-Class Assignment
8.15 Summary 84
In-Class Assignment
Consider the
Bottling.mdb
database, the database of a soft drink bottling plant. – The database manager has observed several defects in production. – The quality expert wants to investigate whether there is a relationship between bottle characteristics (such as material and size) and the number of defects. 1. Create a query that, for each type of material and bottle size, calculates the sum of the fraction of nonconforming bottles (the fraction is the ratio of the number of defective bottles to the total number of bottles). Title the query, “QryMaterials”. Are there any trends?
2. Create a query that, for each type of drink, displays the sum of the fraction of nonconforming bottles. Title the query, “QryDrinkType”. Are there any trends?
85
Overview
8.1 Introduction 8.2 Working with Select Queries 8.3 Queries with Multiple Tables (The Join Operation) 8.4 Working with Operators 8.5 Hands-On Tutorial: A Select Query with Join of Multiple Tables 8.6 Using Total Row (The GroupBy Operation) 8.7 Creating Calculated Fields 8.8 Parameter Queries 8.9 Crosstab Queries 8.10 Action Queries 8.11 Update Queries 8.12 Delete Queries 8.13 Append Queries 8.14 In-Class Assignment
8.15 Summary
86
Summary
A
query
is a question posed against database tables.
Access allows us to build queries using a graphical query grid (Query by-Example or QBE) and by writing SQL statements.
Access queries can be broadly classified into three function-based categories: – Select Queries – Special Purpose Queries (Crosstab, Parameter, and Make-Table queries) – Action Queries (Update, Append, and Delete queries)
Select queries
applications. are the most common type of query used in database – A Select query selects or extracts data from one or more existing tables and displays the selection in the Datasheet View.
87
Summary
(cont.) Query Operations
:
–
Projection
is a vertical slicing of a recordset. – –
Selection
is a horizontal slicing of a recordset.
Sorting
provides an ordered sequence of a recordset. –
Join
is a vertical merging of two tables. – The
GroupBy
operation places similar records together and treats them as one unit or group. A
parameter query
prompts user for criteria values when the query is executed. – The input values are then used in the selection criteria to run the query.
We use
crosstab queries
to group and summarize information and display it in a spreadsheet format. – Create less repetition of information in the datasheet, making it easier to read and analyze the selected field data.
88
Summary
(cont.) Access queries are not restricted to data retrieval Queries that can alter data by performing actions are aptly called
action queries
: – –
Update queries
modify the values of one or more fields in the table.
Delete queries
use criteria to delete one or more records from the table. –
Append queries
append one or more records to an existing table.
89
Additional Links
Review more sample Access Queries from database files on the book website: http://www.dssbooks.com
.
Refer
MS Access 2003: The Complete Reference
by Virginia Andersen for more details on Access Queries.
Also refer
Access 2003 Bible
by Cary N. Prague, Michael R. Irwin, and Jennifer Reardon.
Also see: http://msdn.microsoft.com/ for useful tips on Access Queries.
90