ACCESS CHAPTER 4 Tables and Queries Learning Objectives

ACCESS CHAPTER 4 Tables and Queries Learning Objectives: l Define table structure l Enter data into a table l Alter table structure l Set a table’s field properties l Join tables and establish referential integrity l Create single table queries l Create queries involving multiple tables, derived column values, and expressions l Create queries with an outer join relationship l Create parameter queries

ACCESS OBJECTS: l Tables: All database information is stored in tables comprised of rows (attributes) and columns (tuples) l Queries: A question you ask about one or more tables in your database l Forms: A simple way to enter, change, or display data l 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 Employee. Division. ID 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 integrity. Exercise 4. 13: writing expressions Exercise 4. 7: establishing referential integrity in queries Exercise 4. 8: display related tables Exercise 4. 14: using summary Add all tables to the relationships window; adjust functions in a query their size and position to have the table titles and Exercise 4. 15: creating an outer join contents shown completely as Figure 4. 14; print the query relationships. Exercise 4. 16: creating a parameter TASK 4. set a table’s field properties query Exercise 4. 9: setting field properties for the Complete Problem 3 on page 207 Employee. Work. Phone field and print the resulting dynaset Exercise 4. 10: setting field properties for the Complete Problem 4 on page 207 Employee Commission field and print the first three pages of the resulting dynaset