Web-Enabled Decision Support Systems

1
Web-Enabled Decision Support Systems

• Queries Building Application Foundation

Prof. Name
name_at_email.com Position
[phone] University Name
2
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

3
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

4
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
5
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

6
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

7
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, Email, and DOB fields

The Projection Operation
8
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
9
Sorting

• Sorting provides an ordered sequence of an input
  recordset
• Example
• Input recordset Student table
• Sort StudentID (ascending)

The Sorting Operation
10
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
• Select the Queries option in the Objects Bar to
  display the two popular options for creating a
  new query.

Create Query Options
11
Creating a Query in the Design View (cont.)

1. Double-click on the Create query in Design View
   option to open a new query in the Design View.

New Query Dialog Box
12
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
13
A Select Query Example

• Query
• List the students information (StudentID, Name,
  and Email) for all full time, freshman students
  sorted in descending order of their names.
• Steps
• Projecting student identification number, name,
  and email columns
• Selecting only full time, freshman students
• Sorting selected records in descending order of
  student names

14
Projecting Students Contact Information

• How-to Build a Select Query
• Open the university database and create a new
  query in the Design View.
• Select the table tblStudent and click the Add
  button to add it to the query design.
• Click Close to exit the Show Table dialog box.
• Add the StudentID, Name, and Email fields to the
  Design Grid.

Projecting Fields of the Student Table
15
Saving and Executing a Query

• How-to Build a Select Query (cont.)
• 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.

16
Selecting Freshman and Fulltime Students

• How-to Build a Select Query (cont.)
• Add Freshman criterion for the Class column
  and Full Time criterion for the Type column in
  the Criteria row.
• Uncheck the check boxes in the Show row for the
  Class and Type columns.
• Save and run the query to verify Selection
  operation.

Selecting Rows of the Student Table
17
Sorting Students 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.)
• 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
18
Query Output

• How-to Build a Select Query (cont.)
• Save and run the query to verify Selection
  operation.

Output of the Example Select Query
19
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.

20
Exploring Query Toolbar and Menus
21
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

22
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
23
Hands-On Tutorial Working with Join Queries

• Query
• List the student information (StudentID and
  Name) and name of their departments (not the
  DeptID).
• How-to Design a Query that Involves Multiple
  Tables
• Open the university database and start with a new
  query in the Design View. Add the tblStudent and
  tblDepartment to the Table Pane.
• Add the required fields to the Design Grid.

Joining Student and Department Tables in the
Design View
24
Hands-On Tutorial Working with Join Queries
(cont.)

• Save the query as QryJoinQuery and run it.

The Join Result in the Datasheet View
25
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

26
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

27
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
28
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

29
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

30
Comparison Operators

• We use comparison operators to select a specific
  group of records from a table
• Operators
• gt, gt, lt, lt, , !
• Examples
• CreditHours gt 3.0
• Year 2002

31
The Logical Conditions

• We need logical operators to combine multiple
  criteria
• Operators
• AND, OR, and NOT
• Examples
• Salary gt 7000 AND Salary lt 10000
• Class Sophomore OR Class Freshman
• Class Sophomore AND Type Full Time

32
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

33
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

34
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
• lt 1/1/2001
• Everything on and before January 1, 2001
• lt Now()
• Everything before current date

35
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

36
Hands-On Tutorial A Select Query with Join of
Multiple Tables

• Query
• List the contact information (name, email, 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 students letter grade.
• Tables
• Student table
• Transcript table
• Section table
• Course table

37
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
• Open the university database and start with a new
  query in the Design View. Add the required four
  tables to the Table Pane.
• Add the required fields from their respective
  tables to the Design Grid.

A Select Query Design with Multiple Tables
38
Hands-On Tutorial (cont.) A Select Query with
Join of Multiple Tables

• Add the criteria gt 3.5 for the Grade column.
  Specify the expression ISE for the DeptID
  field.
• Save and run the query to verify the design so
  far.
• Assign the expression gt Year (Now ()) - 6 in the
  Year field.

A Select Query Design with Multiple Tables
39
Hands-On Tutorial (cont.) A Select Query with
Join of Multiple Tables

• Place the final expression Like computer Or
  Like database in the course name field.
• Use the Sort row to specify descending order for
  the Grade column.
• Use the Show row to display only the Name,
  Address, and Email columns. Uncheck all other
  columns in the Show row.
• Save and run the query.

40
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

41
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

42
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
• Open the university database and start with a new
  query in the Design View. Add the student and
  department tables.
• Add the student ID and department name fields to
  the Design Grid.
• Designate this query as a Totals query by
  clicking on the Totals button on the toolbar.
• Click in the Total row under the StudentID
  column, and select the Count option from the
  drop-down list of aggregate functions.

43
Hands-On Tutorial Using the Total Row (cont.)

• The Design View should appear as below

Grouping Student Records by Department in the
Design View
44
Hands-On Tutorial Using the Total Row (cont.)

• Save and run the query for the result shown
  below.
• Aggregation fields (like StudentID in this
  example) are named as ltaggregationgtOfltfield
  namegt.
• Rename the StudentID count column as
  TotalStudents StudentID.

The Output of GroupBy Operation
45
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
• Open the university database and start with a new
  query in the Design View. Add the course and
  section tables.
• Add the Name field from the course table and the
  Evaluation and Year fields from the section table
  to the Design Grid.
• Designate this query as a Total query by clicking
  on the Totals button on the toolbar.

46
Hands-On Tutorial (cont.) Using the Total Row
with the Where Clause

1. Click on the Total row under the Evaluation
   column, and select the Average option from the
   drop-down list of aggregate functions.
2. Add selection criteria (gt Year (Now ()) - 5)
   using the Where option under the Year column.
3. Specify the selection criteria (gt80) for the
   Evaluation column.
4. Remove the check in the Show row for the Year
   column.
5. Save the query as QryCourseEvaluation and run
   it.

The Query Output
47
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
48
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

49
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

50
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
• Open the university database and start with a new
  query in the Design View. Add required tables to
  the Table Pane.
• Add the student Name, StudentID, and CreditHours
  fields to the Design Grid.
• Click the Total button on the toolbar and select
  the Sum aggregate function in the Total row of
  the CreditHours column. Rename this column as
  TCCreditHours.

51
Hands-On Tutorial Creating Calculated Fields
(cont.)

• Add the calculated field TG CreditHoursGrade
  . Choose the Sum aggregate in the Total row.
• Add another calculated field, GPA TG/ TC.
  Assign an Expression option in the Total row for
  this column.

Creating Calculated Fields for GPA Calculations
52
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

53
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
54
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

55
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

56
Parameter Queries (cont.)
The Query Output
The Parameter Query in the Design View
57
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

58
Hands-On Tutorial Working with Parameter
Queries (cont.)

• How-to Work with the Parameter Queries
• Open the university database for a new query in
  the Design View. Add the college, department, and
  faculty tables to the Table Pane.
• Add fields to the Design Grid as shown below.

The Parameter Query with Multiple Parameters
59
Hands-On Tutorial Working with Parameter
Queries (cont.)

1. Enter the Enter the College parameter for the
   CollegeID column. Also, assign the Between
   Starting Date And Ending Date criteria under
   the JoiningDate column.
2. Save and run the query.

The Parameter Query with Multiple Parameters
60
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

61
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
62
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

63
Hands-On Tutorial Working with Crosstab Queries
(cont.)

• How-to Work with Crosstab Queries
• Open the university database with the new query,
  and add the course, section, and transcript
  tables to the Table Pane.
• Add the CourseID, Name, and DeptID fields from
  the course table, the StudentID field from the
  transcript table, and the Sem field from the
  section table.
• 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.
• Select the Row Heading option for the CourseID
  and Name columns in the Crosstab row.

64
Hands-On Tutorial Working with Crosstab Queries
(cont.)

1. Select the Column Heading option for the Sem
   field.
2. Select the Value option for the StudentID field.
   Also choose the Count function in the Total row
   for the StudentID column.
3. Specify the department selection criteria
   (CISE) under DeptID column.

The Crosstab Query (Students per Semester per
Course) in the Design View
65
Hands-On Tutorial Working with Crosstab Queries
(cont.)

• Save and then run the query to produce the result
  shown

The Output of the Crosstab Query (Students per
Semester per Course)
66
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

67
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

68
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

69
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 After
Faculty Table Before
70
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
71
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
• Open the university database and begin with a new
  query in the Design View. Add the faculty table
  to the query design.
• Change the query type to the Update query.
• Add the Salary and JoiningDate fields from the
  table list to the Design Grid. In the Salary
  field, enter the expression Salary1.05 under
  the Update To row.

72
Hands-On Tutorial Working with an Update Query
(cont.)

1. In the JoiningDate field, specify the following
   criteria lt 1/1/1995. In the Salary field,
   enter the criteria lt 70,000.
2. 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
73
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
74
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
75
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

76
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
Preview of the Delete Query Displaying Records
to be Deleted
77
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
• Open the university database and start with the
  new query in the Design View. Add the student and
  department tables to the query design.
• Change the query type from the default Select
  query to the Delete query.
• Add the Class and DeptID fields from the student
  and department lists, respectively, to the Design
  Grid.

78
Hands-On Tutorial Building a Delete Query
(cont.)

1. Add all fields from the student tables by adding
   an asterisk () to the Design Grid.
2. Specify the criteria fields for Class as
   Senior and for DeptID as ISE.
3. Save the query as qryDelStudents. Preview the
   deleted records using the View button on the
   toolbar.

The Delete Query in the Design View
79
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

80
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

81
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
Preview of the Append Query
82
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
• Start with a new query and add the student,
  department, and college tables to the query
  design.
• 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
83
Hands-On Tutorial Building an Append Query
(cont.)

1. Add all the required fields and the selection
   criteria as shown below.
2. 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
84
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

85
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.
• 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?
• 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?

86
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

87
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 are the most common type of query
  used in database applications.
• A Select query selects or extracts data from one
  or more existing tables and displays the
  selection in the Datasheet View.

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

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

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