Transcript DBC-e03-Access-Workbench-Section-03-PP

DAVID M. KROENKE and DAVID J. AUER
DATABASE CONCEPTS, 3rd Edition
The Access Workbench: Section Three
Working with Queries in Microsoft Access
Section Objectives
 Learn how to use Microsoft Access to:
 Run Access SQL statements
 Run queries in single and multiple tables in
using both SQL and Query By Example
(QBE)
 Manually set table and relationship
properties not supported by Access SQL
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-2
Using Access QBE:
The SQL Query to be Run
SELECT * FROM CUSTOMER;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-3
Using Access SQL:
The Queries Pane
The Queries Pane
Double-click Create query in
Design view to create a new
query
Click Queries to display the
Queries Pane
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-4
Using Access SQL:
The Show Table Dialog Box
Query1: Select Query window
Show Table Dialog Box
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-5
Using Access SQL:
The Query1: Select Query Window in Design View
SQL View button
Query1: Select Query window
in Design View
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-6
Using Access SQL:
The Query1: Select Query Window in SQL View
Design View button
Query1: Select Query window
in SQL View
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-7
Using Access SQL:
The SQL Query
The Save Query
button
The Execute Query button
The SQL query text
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-8
Using Access SQL:
The Query Results
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-9
Using Access SQL:
The Save As Dialog Box
The Save Query
button
The Save As dialog box
Type new query name here
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-10
Using Access SQL:
The SQLQuery-AW-3-01: Select Query Window
The query has been
renamed as
SQLQuery-AW-3-01
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-11
Using Access SQL:
The SQLQuery-AW-3-01 Object in the Queries Pane
The SQLQuery-AW-3-01 query
object
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-12
Using Access QBE:
The SQL Query to be Run
SELECT * FROM CUSTOMER;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-13
Using Access QBE:
The Show Table Dialog Box
Query1: Select Query window
Show Table Dialog Box
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-14
Using Access QBE:
The QBE Query1 : Select Query Window
Tables in the query appear in
the top pane, together with a list
of their columns (the “field list”)
and an asterisk (*) meaning “all
columns”
Columns in the query are called
fields and appear in the bottom
pane, together with related
property values
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-15
Using Access QBE:
Adding Columns to the Query
To add a column to the query,
click the column name and drag
it to a cell in the Field: row in the
lower window pane
The asterisk (*) symbol was
dragged and dropped here to
add the CUSTOMER.* field to
the query
The table name is automatically
added to the query to specify the
source of the column–this is
important if there is more than
one table in the query with the
same column name
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-16
Using Access QBE:
The Query Results
The results are
sorted by
CustomerID
As expected, the
query results are
identical to those
shown in the SQL
query
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-17
Using Access QBE:
The SQL Query to be Run
SELECT
FROM
CustomerID, LastName, FirstName
CUSTOMER;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-18
Using Access QBE:
Selecting Individual Fields
The CustomerID, LastName, and
FirstName fields are in the query
The results will be sorted by LastName
in descending order (Z–A)
The results will show only customers
with a CustomerID greater than 2
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-19
Using Access QBE:
The SQL Query to be Run
SELECT
FROM
WHERE
ORDER BY
LastName, FirstName, Date, Type, Remarks
CUSTOMER, CONTACT
CUSTOMER.CustomerID = CONTACT.CustomerID
AND
CustomerID = 3
Date;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-20
Using Access QBE:
The Query Window with the Two Tables
Both the CONTACT table and
CUSTOMER table are in the query
The relationship and referential integrity constrain between the two tables is
automatically included in the query—this means that the SQL clause
WHERE CUSTOMER.CustomerID = CONTACT.CustomerID
is automatically part of the query
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-21
Using Access QBE:
The Complete Two Table Query
From CUSTOMER, the CustomerID,
LastName, and FirstName columns
are in the query
From CONTACT, the Date, Type,
and Remarks columns are in the query
The results will be sorted by Date in
ascending order (A–Z)
The results will be shown only for the
customer with a CustomerID of 3
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-22
Using Access QBE:
The Two Table Query Results
The results are sorted by Date in ascending
order (earliest date to latest date)
The results are shown for the customer with the
CustomerID of 3, which is Jessica Christman
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-23
Using Access QBE:
The SQL Query to be Run
SELECT
FROM
WHERE
ORDER BY
LastName, FirstName, Date, Type, Remarks
CUSTOMER, CONTACT
CUSTOMER.CustomerID = CONTACT.CustomerID
AND
CustomerID = [?]
Date;
The query now contains an unknown
value for CustomerID—we will use a
query with a prompt that will be
displayed to allow the user to enter a
parameter value when the query is
run
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-24
Using Access QBE:
Using a Parameterized Query
The CustomerID column is now
shown last because it is not
displayed in the query results
The Criteria for the CustomerID
column now contains the text for a
prompt to be displayed in the Enter
Parameter Value dialog box that will
be displayed to get a parameter
value from the user
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-25
Using Access QBE:
The Enter Parameter Value Dialog Box
This is the text that was entered into
the criteria field for CustomerID
Enter the CustomerID number here
Click the OK button to run the query
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-26
Using Access QBE:
The Two Table Query Results
The user input the parameter value of 3, so the
results are shown for the customer with the
CustomerID of 3, which is Jessica Christman
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-27
SALESPERSON Table Column Characteristics
SALESPERSON
Column Name
Type
Key
Required
NickName
Text (35)
Primary Key
Yes
LastName
Text (25)
No
Yes
FirstName
Text (25)
No
Yes
HireDate
Date/Time
No
Yes
WageRate
Number
No
Yes
Double, Default
value = $12.50
CommissionRate
Number
No
Yes
Double
Phone
Text (12)
No
Yes
Email
Text (100)
No
Yes
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
Remarks
Unique
AW-3-28
SALESPERSON Data
NickName
LastName
FirstName
HireDate
WageRate
Commission
Rate
Tina
Smith
Tina
10-AUG-04
15.50
12.5%
Big Bill
Jones
William
25-SEP-04
15.50
12.5%
Billy
Jones
Bill
17-MAY-05
12.50
12.0%
NickName
Phone
Email
Tina
206-287-7010
[email protected]
Big Bill
206-287-7020
[email protected]
Billy
206-287-7030
[email protected]
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-29
SQL to Create the SALESPERSON Table
CREATE TABLE SALESPERSON(
NickName
Char(35)
NOT NULL,
LastName
Char(25)
NOT NULL,
FirstName
Char(25)
NOT NULL,
HireDate
DateTime
NOT NULL,
WageRate
Numeric(5,2)
NOT NULL
DEFAULT(12.50),
CommissionRate Numeric(5,3)
NOT NULL,
Phone
Char(12)
NOT NULL,
Email
Varchar(100)
NOT NULL
UNIQUE,
CONSTRAINT
SALESPERSON_PK
PRIMARY KEY(NickName)
);
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-30
SQL to Create the SALESPERSON Table
Modified for ACCESS ANSI-89 SQL
CREATE TABLE SALESPERSON(
NickName
Char(35)
NOT NULL,
LastName
Char(25)
NOT NULL,
FirstName
Char(25)
NOT NULL,
HireDate
DateTime
NOT NULL,
WageRate
Numeric
NOT NULL,
CommissionRate Numeric
NOT NULL,
Phone
Char(12)
NOT NULL,
Email
Varchar(100)
NOT NULL,
CONSTRAINT
SALESPERSON_PK
PRIMARY KEY(NickName)
);
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-31
The CREATE TABLE SALESPERSON SQL Statement
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-32
The Create-Table SALESPERSON Query
in the Queries Pane
Click the Tables button to move to the
Tables pane
The Create-Table-SALESPERSON
query–note the Design icon that identifies
this as a data definition query
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-33
The SALESPERSON Table
in the Tables Pane
After selecting a table, click the Design
View button to open the table in Design
View
The SALESPERSON table object–click
the object to select it
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-34
The SALESPERSON Table in Design View
Move the mouse cursor here, then click
to select the WageRate column (field)
The General tab in the Field Properties
section
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-35
The WageRate Field Properties
The WageRate column is selected
The Format text box
The Decimal Places text box
The Default Value text box
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-36
The Format Text Box
Click in the Format text box to select it
Click the Format text box drop-down
arrow to display the drop-down list
Select Currency
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-37
The Default Value Text Box
The Property Update Options icon—
you can simply ignore it
Click in the Default Value text box to
select it
The Expression Builder icon—we will
not use it in this situation
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-38
The Completed WageRate Field Properties
Click the Save button to save the change
to the SALESPERSON table design
Data format is set to Currency
Number of decimal places is set to 2
The default value is set to 12.50
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-39
The Email Field Properties
The Email column is selected
The Indexed text box
Select Yes (No Duplicates)
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-40
Specifying a Validation Rule
Enter the possible values for the column
in the Validation Rule text box
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-41
SQL to Insert Data into the
SALESPERSON Table
INSERT INTO SALESPERSON
VALUES('Tina', 'Smith', 'Tina',
'10-AUG-04', 15.50, .125, '206-287-7010',
'[email protected]');
INSERT INTO SALESPERSON
VALUES('Big Bill', 'Jones', 'William',
'25-SEP-04', 15.50, .125, '206-287-7020',
'[email protected]');
INSERT INTO SALESPERSON
VALUES('Billy', 'Jones', 'Bill',
'17-MAY-05', 12.50, .120, '206-287-7030',
'[email protected]');
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-42
Inserting Data Into The
SALESPERSON Table
The INSERT command
The dialog box confirming the INSERT
Click the Yes button to complete the
INSERT
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-43
The Data in the SALESPERSON Table
The data is sorted by NickName (the
primary key value) in ascending order
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-44
CUSTOMER.NickName
Column Characteristics
CUSTOMER
Column Name
Type
Key
Required
NickName
Text (35)
Foreign Key
Yes
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
Remarks
AW-3-45
CUSTOMER.NickName Data
CustomerID
LastName
FirstName
...
NickName
1
Griffey
Ben
...
Big Bill
3
Christman
Jessica
...
Billy
4
Christman
Rob
...
Tina
5
Hayes
Judy
...
Tina
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-46
SQL to Alter the CUSTOMER Table
ALTER TABLE CUSTOMER
ADD NickName Char(35) NULL;
UPDATE CUSTOMER
SET
NickName = 'Big Bill'
WHERE
CustomerID = 1;
UPDATE CUSTOMER
SET
NickName = 'Billy'
WHERE
CustomerID = 3;
UPDATE CUSTOMER
SET
NickName = 'Tina'
WHERE
CustomerID = 4;
UPDATE CUSTOMER
SET
NickName = 'Tina'
WHERE
CustomerID = 5;
ALTER TABLE CUSTOMER
ALTER COLUMN NickName Char(35) NOT NULL;
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-47
The Altered CUSTOMER Table
The added NickName column
The column is required, which is the
Access equivalent of NOT NULL
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-48
The Relationships Window
with the Current Relationship Diagram
The Show Table button
This is the relationship
diagram as we created it in
Section 2’s “The Access
Workbench”
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-49
The Updated Relationship Diagram
The relationship between
SALESPERSON and
CUSTOMER already
exists—it was created using
SQL—so right-click the
relationship line and then
click Edit Relationship in
the shortcut menu that
appears to display the Edit
Relationships dialog box
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-3-50
The Completed
Edit Relationships Dialog Box
The Enforce Referential
Integrity check box is
already selected because of
the SQL CONSTRAINT
FOREIGN KEY statement
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
Check the Cascade Update
Related Fields check box to
set the equivalent of the SQL
ON UPDATE CASCADE
statement
Leave the Cascade Delete
Related Fields check box
unchecked—this is
equivalent to the SQL ON
DELETE NO ACTION
statement
AW-3-51
DAVID M. KROENKE and DAVID J. AUER
DATABASE CONCEPTS, 3rd Edition
End of Presentation on The Access Workbench: Section Three
Working with Queries in Microsoft Access