1 web-enabled decision support systems queries: building application foundation prof. name...
TRANSCRIPT
![Page 1: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/1.jpg)
1
Web-Enabled Decision Support Systems
Queries: Building Application Foundation
Prof. Name [email protected] (123) 456-7890University Name
![Page 2: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/2.jpg)
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
![Page 3: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/3.jpg)
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
![Page 4: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/4.jpg)
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
![Page 5: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/5.jpg)
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
![Page 6: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/6.jpg)
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
![Page 7: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/7.jpg)
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
![Page 8: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/8.jpg)
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
![Page 9: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/9.jpg)
9
Sorting
Sorting provides an ordered sequence of an input recordset– Example:
Input recordset: Student table
Sort: StudentID (ascending)
The Sorting Operation
![Page 10: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/10.jpg)
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
1. Select the Queries option in the Objects Bar to display the two popular options for creating a new query.
“Create Query” Options
![Page 11: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/11.jpg)
11
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
![Page 12: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/12.jpg)
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
![Page 13: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/13.jpg)
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:
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
![Page 14: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/14.jpg)
14
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 and click the Add button to add it to the query design.
3. Click Close to exit the Show Table dialog box.
4. Add the StudentID, Name, and Email fields to the Design Grid.
Projecting Fields of the Student Table
![Page 15: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/15.jpg)
15
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.
![Page 16: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/16.jpg)
16
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 row for the Class and Type columns.
8. Save and run the query to verify Selection operation.
Selecting Rows of the Student Table
![Page 17: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/17.jpg)
17
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
![Page 18: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/18.jpg)
18
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
![Page 19: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/19.jpg)
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.
![Page 20: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/20.jpg)
20
Exploring Query Toolbar and Menus
Picture Name Functionality Main Menu Short-cut Menu
Design
View
Switches to the
Design View
View | Design View Table Pane | Design View
Query
Type
Provides options to
choose query type
Query | {Query
Type Name}
Table Pane | Query Type |
{Query Type Name}
Show
Table
Opens the Show
Table dialog box to
add tables/queries
Query | Show Table Table Pane | Show Table
Totals Adds Total row to the
Design Grid for total
queries
View | Totals -
Properties Opens the Query
Properties window
View | Properties Design Grid | Properties
Build Opens the
Expression Builder
dialog box
- Criteria row | Build
![Page 21: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/21.jpg)
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
![Page 22: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/22.jpg)
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
![Page 23: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/23.jpg)
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
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
![Page 24: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/24.jpg)
24
Hands-On Tutorial: Working with Join Queries (cont.)
3. Save the query as “QryJoinQuery” and run it.
The Join Result in the Datasheet View
![Page 25: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/25.jpg)
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
![Page 26: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/26.jpg)
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
![Page 27: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/27.jpg)
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
![Page 28: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/28.jpg)
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
![Page 29: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/29.jpg)
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
![Page 30: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/30.jpg)
30
Comparison Operators
We use comparison operators to select a specific group of records from a table – Operators:
>, >=, <, <=, =, !=
Examples:– CreditHours > 3.0
– Year = 2002
![Page 31: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/31.jpg)
31
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”
![Page 32: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/32.jpg)
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
![Page 33: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/33.jpg)
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”
![Page 34: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/34.jpg)
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:– <= #1/1/2001#
Everything on and before January 1, 2001
– < Now() Everything before current date
![Page 35: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/35.jpg)
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
![Page 36: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/36.jpg)
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 student’s letter grade.”
Tables:– Student table
– Transcript table
– Section table
– Course table
![Page 37: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/37.jpg)
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 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
![Page 38: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/38.jpg)
38
Hands-On Tutorial (cont.): A Select Query with Join of Multiple Tables
3. Add the criteria >= 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.
4. Assign the expression > Year (Now ()) - 6 in the Year field.
A Select Query Design with Multiple Tables
![Page 39: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/39.jpg)
39
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 Email columns. Uncheck all other columns in the Show row.
8. Save and run the query.
![Page 40: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/40.jpg)
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
![Page 41: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/41.jpg)
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
![Page 42: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/42.jpg)
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
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 query by clicking on the Totals button on the toolbar.
4. Click in the Total row under the StudentID column, and select the Count option from the drop-down list of aggregate functions.
![Page 43: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/43.jpg)
43
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
![Page 44: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/44.jpg)
44
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
<aggregation>Of<field name>.
7. Rename the StudentID count column as “TotalStudents: StudentID”.
The Output of GroupBy Operation
![Page 45: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/45.jpg)
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 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 Evaluation and Year fields from the section table to the Design Grid.
3. Designate this query as a Total query by clicking on the Totals button on the toolbar.
![Page 46: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/46.jpg)
46
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 Show row for the Year column.
8. Save the query as “QryCourseEvaluation” and run it.
The Query Output
![Page 47: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/47.jpg)
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
![Page 48: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/48.jpg)
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
![Page 49: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/49.jpg)
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]
![Page 50: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/50.jpg)
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 Queries1. 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 fields to the Design Grid.
3. 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 TC:CreditHours.
![Page 51: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/51.jpg)
51
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
![Page 52: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/52.jpg)
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
![Page 53: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/53.jpg)
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
![Page 54: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/54.jpg)
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
![Page 55: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/55.jpg)
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]
![Page 56: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/56.jpg)
56
Parameter Queries (cont.)
The Parameter Query in the Design View
The Query Output
![Page 57: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/57.jpg)
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
![Page 58: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/58.jpg)
58
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
![Page 59: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/59.jpg)
59
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
![Page 60: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/60.jpg)
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
![Page 61: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/61.jpg)
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
![Page 62: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/62.jpg)
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
![Page 63: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/63.jpg)
63
Hands-On Tutorial: Working with Crosstab Queries (cont.)
How-to: Work with Crosstab Queries1. 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 Sem field from the section table.
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.
![Page 64: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/64.jpg)
64
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 field. Also choose the Count function in the Total row for the StudentID column.
7. Specify the department selection criteria (=“CISE”) under DeptID column.
The Crosstab Query (Students per Semester per Course) in the Design View
![Page 65: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/65.jpg)
65
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)
![Page 66: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/66.jpg)
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
![Page 67: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/67.jpg)
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
![Page 68: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/68.jpg)
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
![Page 69: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/69.jpg)
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 Before Faculty Table After
![Page 70: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/70.jpg)
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
![Page 71: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/71.jpg)
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 Queries1. 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 fields from the table list to the Design Grid. In the Salary field, enter the expression [Salary]*1.05 under the Update To row.
![Page 72: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/72.jpg)
72
Hands-On Tutorial: Working with an Update Query (cont.)
4. In the JoiningDate field, specify the following criteria: “< #1/1/1995#”. In the Salary 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
![Page 73: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/73.jpg)
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
![Page 74: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/74.jpg)
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
![Page 75: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/75.jpg)
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
![Page 76: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/76.jpg)
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
![Page 77: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/77.jpg)
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 Queries1. 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.
![Page 78: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/78.jpg)
78
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
![Page 79: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/79.jpg)
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
![Page 80: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/80.jpg)
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
![Page 81: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/81.jpg)
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
![Page 82: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/82.jpg)
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 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
![Page 83: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/83.jpg)
83
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
![Page 84: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/84.jpg)
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
![Page 85: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/85.jpg)
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.
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?
![Page 86: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/86.jpg)
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
![Page 87: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/87.jpg)
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.
![Page 88: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/88.jpg)
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.
![Page 89: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/89.jpg)
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.
![Page 90: 1 Web-Enabled Decision Support Systems Queries: Building Application Foundation Prof. Name name@email.com Position (123) 456-7890 University Name](https://reader037.vdocuments.us/reader037/viewer/2022110211/56649eef5503460f94bfed14/html5/thumbnails/90.jpg)
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.