ACCESS CHAPTER 4 Tables and Queries

1
ACCESS CHAPTER 4Tables and Queries

• Learning Objectives
• Define table structure
• Enter data into a table
• Alter table structure
• Set a tables 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

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

3
ACCESS CHAPTER 4Tables and Queries
TABLES
QUERIES

• TASK 1. create a table from scratch and enter
  data into it
• Exercise 4.1 creating the Customer Table
  structure
• Exercise 4.2 defining the table
• Exercise 4.3 4.4 populating the Customer
  Table
• Print Customer Table
• TASK 2. alter table structure - adding a column
  to a table
• Exercise 4.5 adding a column to the Employee
  Table
• Exercise 4.6 entering values in the
  EmployeeDivisionID column
• TASK 3. establish referential integrity
• Prohibiting removal of a parent table row until
  all the rows in another table referring to the
  parent table are first removed in called
  referential integrity.
• Exercise 4.7 establishing referential integrity
• Exercise 4.8 display related tables
• Add all tables to the relationships window
  adjust their size and position to have the table
  titles and contents shown completely as Figure
  4.14 print the relationships.
• TASK 4. set a tables field properties
• Exercise 4.9 setting field properties for the
  EmployeeWorkPhone field
• Exercise 4.10 setting field properties for the
  Employee Commission field

TASK 1. create and use a query Exercise 4.11
creating a one-table query Exercise 4.12
creating a two-table query using a comparison
operator Complete Exercise 4 on page 206 and
print the resulting dynaset Read Using Logical
Operator in Query Criteria section on page
192 Complete Problem 2 on page 207 and print the
resulting dynaset TASK 2. perform calculations
with queries Exercise 4.13 writing expressions
in queries Exercise 4.14 using summary
functions in a query Exercise 4.15 creating an
outer join query Exercise 4.16 creating a
parameter query Complete Problem 3 on page 207
and print the resulting dynaset Complete Problem
4 on page 207 and print the first three pages of
the resulting dynaset