Exploring Microsoft Office 2016 Series Editor Mary Anne

  • Slides: 57
Download presentation
Exploring Microsoft® Office 2016 Series Editor Mary Anne Poatsy Mulbery|Krebs|Hogan|Cameron |Davidson|Lau|Lawson|Williams Series Created by

Exploring Microsoft® Office 2016 Series Editor Mary Anne Poatsy Mulbery|Krebs|Hogan|Cameron |Davidson|Lau|Lawson|Williams Series Created by Dr. Robert T. Grauer

Exploring Access 2016 Chapter 2

Exploring Access 2016 Chapter 2

Objective 1: Design a Table Skills: Design a Table

Objective 1: Design a Table Skills: Design a Table

Design a Table • Good database design begins with the tables because tables provide

Design a Table • Good database design begins with the tables because tables provide the framework for all the activities you perform in a database. • As you design a table, you should consider all of the specific fields that you will need and the type of data each field will store such as numbers, dates, and so forth.

Design a Table • Suppose that you are designing a database for a bank

Design a Table • Suppose that you are designing a database for a bank which includes three tables: § Customers table: store each customer’s information (name, phone, address, and so on) § Accounts table: store the account information of each customer. § Branches table: store each branch’s information

Design a Table • After identifying the tables that you need, add the necessary

Design a Table • After identifying the tables that you need, add the necessary fields using the following as a guideline: § Include the necessary data. § Design for now and the future. § Store data in their smallest parts. § Determine the primary keys. § Link tables using common fields. § Design to accommodate calculations.

Design a Table • (1) Include the necessary data. • A good way to

Design a Table • (1) Include the necessary data. • A good way to determine what data are necessary in tables is to consider the output you will need from your database. • For example, you want to create a professionallooking report from the Branch table. In this report, you want to determine when each branch starts. • So, you must include a field to store the start date of each branch.

Design a Table • (2) Design for Now and for the Future. • As

Design a Table • (2) Design for Now and for the Future. • As the information requirements of an organization evolve over time, the database systems that hold the data must change as well. • When designing a database, try to anticipate the future needs of the system and build in the flexibility to satisfy those demands. • For example, you may also decide to create additional fields for future use (such as an email or customer photo field) in the customers table.

Design a Table • (3) Store data in their smallest parts. • The customers

Design a Table • (3) Store data in their smallest parts. • The customers table divides a customer’s name into two fields (First. Name and Last. Name) to store each value individually. • It is not a good design to use a single field consisting of both the first name and last name. • Consider a customer name: Sue Grater. • You could not easily create a salutation for a letter using the form Dear Sue or Dear Dr. Grater because the first name and the last name are not accessible individually.

Design a Table • (3) Determine primary keys. • The primary key must be

Design a Table • (3) Determine primary keys. • The primary key must be able to uniquely identify each record in a table. • In the customers table, you cannot use the customer’s name as the primary because some customers may have the same name. • The Customer. ID field will uniquely identify each customer by assigning a unique Customer. ID to a customer just as the university assigns a student ID to you.

Design a Table • (4) Plan for common fields between tables. • Keep in

Design a Table • (4) Plan for common fields between tables. • Keep in mind that some tables will be joined in relationships using common fields. • For example, Customer. ID in the Customers table will join to the Customer. ID field in the Accounts table. • That means, the Customers table and the Account table are joined in relationships using Customer. ID.

Design a Table • (4) Plan for common fields between tables. • The common

Design a Table • (4) Plan for common fields between tables. • The common field will generally be the primary key in one table (such as Customer. ID in Customers table): the same field in the adjoining table is denoted as the foreign key. • For example, Customer. ID is the foreign key in the Account table.

Design a Table • (5) Design to accommodate calculations. • Calculated fields are frequently

Design a Table • (5) Design to accommodate calculations. • Calculated fields are frequently created in database objects with numeric data, such as a monthly interest field that multiplies the balance in a customer’s account by 1% each month.

Objective 2: Create and Modify Tables and Work with Data Skills: Create a Table

Objective 2: Create and Modify Tables and Work with Data Skills: Create a Table in Datasheet View Delete a Field Set a Table’s Primary Key Work with Field Properties Create a New Field in Design View Modify the Table in Datasheet View

Create and Modify Tables and Work with Data • Tables can be created in

Create and Modify Tables and Work with Data • Tables can be created in a new blank database or an existing database. • There are several methods for creating a table. You could use a template or import data from another database or application such as Excel. • You could use Design view to type the field names and then use Datasheet view to enter the data. • Or you could simply enter the field names and table data directly using Datasheet view.

Create and Modify Tables and Work with Data • Field properties determine how the

Create and Modify Tables and Work with Data • Field properties determine how the field looks and behaves. • Field properties are often set in Design view, but some properties can be set in Datasheet view on the Table Tools Fields tab.

Create and Modify Tables and Work with Data • You can add new fields

Create and Modify Tables and Work with Data • You can add new fields using Datasheet view or Design view. • You can also delete a field using Datasheet view or Design view.

Create and Modify Tables and Work with Data • Design view is typically used

Create and Modify Tables and Work with Data • Design view is typically used to create and modify table structure such as adding and editing fields or setting field properties. • Datasheet view is used to add, edit, and delete records

Exercise -1 • Task 1: Create a table in datasheet view • a. Start

Exercise -1 • Task 1: Create a table in datasheet view • a. Start Microsoft Office Access 2016 and click Blank desktop database. • b. Type a 02 h 1 Bank_Last. First into the File Name box. • c. Click Browse to find the “assignment” folder on the desktop and click OK. Click Create to create the new database. (Access will create the new database named a 02 h 1 Bank_Last. First and a new table will automatically open in Datasheet view. )

Exercise -1 • Task 1: Create a table in datasheet view

Exercise -1 • Task 1: Create a table in datasheet view

Exercise -1 • Task 1: Create a table in datasheet view

Exercise -1 • Task 1: Create a table in datasheet view

Exercise -1 • Task 2: Delete a field and set a table’s primary key

Exercise -1 • Task 2: Delete a field and set a table’s primary key

Exercise -1 • Task 2: Delete a field and set a table’s primary key

Exercise -1 • Task 2: Delete a field and set a table’s primary key

Exercise -1 • Task 3: Work with field properties

Exercise -1 • Task 3: Work with field properties

Exercise -1 • Task 3: Work with field properties

Exercise -1 • Task 3: Work with field properties

Exercise -1 • Task 4: Create a new field in design view

Exercise -1 • Task 4: Create a new field in design view

Exercise -1 • Task 4: Create a new field in design view

Exercise -1 • Task 4: Create a new field in design view

Exercise -1 • Task 4: Create a new field in design view

Exercise -1 • Task 4: Create a new field in design view

Exercise -1 • Task 5: Modify the table in datasheet view

Exercise -1 • Task 5: Modify the table in datasheet view

Exercise -1 • Task 5: Modify the table in datasheet view

Exercise -1 • Task 5: Modify the table in datasheet view

Exercise -1 • Task 5: Modify the table in datasheet view • Close the

Exercise -1 • Task 5: Modify the table in datasheet view • Close the database and exit Access.

Objective 3: Share Data Skills: Import Excel Data Import Data from an Access Database

Objective 3: Share Data Skills: Import Excel Data Import Data from an Access Database Modify an Imported Table’s Design Add Data to an Imported Table

Share Data • Another method to create tables is by importing data from Excel.

Share Data • Another method to create tables is by importing data from Excel. Although Excel spreadsheets are used to store data, often the data can be more efficiently managed in a database. Access provides a wizard that guides you through the process of importing data from Excel.

Share Data • You also can use a wizard to import tables, queries, forms,

Share Data • You also can use a wizard to import tables, queries, forms, reports, pages, macros, and modules from other databases. The wizard is located on the External Data tab in the Import & Link group.

Share Data • Importing data from other applications saves typing and prevents errors while

Share Data • Importing data from other applications saves typing and prevents errors while entering data. However, modifications may need to be made to the imported tables. • For example, fields may need to be renamed so they are more meaningful. Use Design view to make changes to the data types, field sizes, captions, input masks, and other properties.

Share Data • After making modifications in Design view, switch back to Datasheet view.

Share Data • After making modifications in Design view, switch back to Datasheet view. The design changes have been saved, and you are ready to use Datasheet view to add or modify records.

Objective 4: Establish Table Relationships Skills: Establish Table Relationships Enforce Referential Integrity

Objective 4: Establish Table Relationships Skills: Establish Table Relationships Enforce Referential Integrity

Establish Table Relationships • One of the benefits of a relationship is to efficiently

Establish Table Relationships • One of the benefits of a relationship is to efficiently combine data from related tables for the purpose of creating queries, forms, and reports. • The common fields that were determined in the design phase of the tables can be used to establish relationships between them.

Establish Table Relationships • Referential integrity enforces the rules in a database that are

Establish Table Relationships • Referential integrity enforces the rules in a database that are used to preserve relationships between tables when records are changed. • When referential integrity is enforced, you cannot enter a foreign key value in a related table unless the primary key value exists in the primary table.

Exercise-2 • Task 1: Import excel data • Download “a 02 h 2 Customers

Exercise-2 • Task 1: Import excel data • Download “a 02 h 2 Customers ” • a. Download “a 02 h 1 Bank_First. Last”, open it, and save is as a 02 h 2 Bank_Last. First, changing h 1 to h 2. • b. If necessary, click Enable Content below the Ribbon to indicate you trust the contents of the database. •

Exercise-2 • Task 1: Import excel data • d. Click Browse and go to

Exercise-2 • Task 1: Import excel data • d. Click Browse and go to “assignment” folder. Select the a 02 h 2 Customers workbook. Click Open and click OK to open the Import Spreadsheet Wizard. •

Exercise-2 • Task 1: Import excel data •

Exercise-2 • Task 1: Import excel data •

Exercise-2 • Task 1: Import excel data •

Exercise-2 • Task 1: Import excel data •

Exercise-2 • Task 1: Import excel data •

Exercise-2 • Task 1: Import excel data •

Exercise-2 • Task 2: Import data from an access database Download “a 02 h

Exercise-2 • Task 2: Import data from an access database Download “a 02 h 2 Accounts“. b. Click Browse and go to the “assignment” folder. Select the a 02 h 2 Accounts database. Click Open and click OK to open the Import Objects dialog box. (The Accounts table is active; you will import this table. )

Exercise-2 • Task 2: Import data from an access database

Exercise-2 • Task 2: Import data from an access database

Exercise-2 • Task 3: Modify an imported table’s design and add data

Exercise-2 • Task 3: Modify an imported table’s design and add data

Exercise-2 • Task 3: Modify an imported table’s design and add data

Exercise-2 • Task 3: Modify an imported table’s design and add data

Exercise-2 • Task 3: Modify an imported table’s design and add data

Exercise-2 • Task 3: Modify an imported table’s design and add data

Exercise-2 • Task 3: Modify an imported table’s design and add data

Exercise-2 • Task 3: Modify an imported table’s design and add data

Exercise-2 • Task 3: Modify an imported table’s design and add data

Exercise-2 • Task 3: Modify an imported table’s design and add data

Exercise-2 • Task 4: Add data to an imported table

Exercise-2 • Task 4: Add data to an imported table

Exercise-2 • Task 4: Add data to an imported table

Exercise-2 • Task 4: Add data to an imported table

Exercise-2 • Task 4: Add data to an imported table

Exercise-2 • Task 4: Add data to an imported table

Exercise-2 • Task 5: Establish table relationships

Exercise-2 • Task 5: Establish table relationships

Exercise-2 • Task 5: Establish table relationships

Exercise-2 • Task 5: Establish table relationships

Exercise-2 • Task 5: Establish table relationships

Exercise-2 • Task 5: Establish table relationships