Transcript ACCESS CHAPTER 4 Tables and Queries Learning Objectives

ACCESS CHAPTER 4
Tables and Queries
Learning Objectives:
 Define table structure
 Enter data into a table
 Alter table structure
 Set a table’s field properties
 Join tables and establish referential integrity
 Create single table queries
 Create queries involving multiple tables,
derived column values, and expressions
 Create queries with an outer join relationship
 Create parameter queries
ACCESS OBJECTS:




Tables: All database information is stored in
tables comprised of rows (attributes) and
columns (tuples)
Queries: A question you ask about one or
more tables in your database
Forms: A simple way to enter, change, or
display data
Reports: A convenient way to consolidate and
group information that is stored in a database
for publication or reporting purposes.
TABLES
ACCESS CHAPTER 4
Tables and Queries
QUERIES
TASK 1. create a table from scratch and enter data into it TASK 1. create and use a query
Exercise 4.1: creating the ‘Customer Table’ structure
Exercise 4.11: creating a one-table
query
Exercise 4.2: defining the table
Exercise 4.12: creating a two-table
Exercise 4.3 & 4.4: populating the ‘Customer Table’
query using a comparison operator
Print ‘Customer Table’
Complete Exercise 4 on page 206
TASK 2. alter table structure - adding a column to a table
and print the resulting dynaset
Exercise 4.5: adding a column to the ‘Employee Table’
Read ‘Using Logical Operator in
Exercise 4.6: entering values in the
Query Criteria’ section on page 192
EmployeeDivisionID column
Complete Problem 2 on page 207
TASK 3. establish referential integrity
and print the resulting dynaset
Prohibiting removal of a parent table row until all
TASK 2. perform calculations with
the rows in another table referring to the parent
queries
table are first removed in called referential
Exercise 4.13: writing expressions
integrity.
in queries
Exercise 4.7: establishing referential integrity
Exercise 4.14: using summary
Exercise 4.8: display related tables
functions in a query
Add all tables to the relationships window; adjust
Exercise 4.15: creating an outer join
their size and position to have the table titles and
query
contents shown completely as Figure 4.14; print the
Exercise 4.16: creating a parameter
relationships.
query
TASK 4. set a table’s field properties
Complete Problem 3 on page 207
Exercise 4.9: setting field properties for the
and print the resulting dynaset
EmployeeWorkPhone field
Complete Problem 4 on page 207
Exercise 4.10: setting field properties for the
and print the first three pages of
Employee Commission field
the resulting dynaset