Exploring Microsoft Office 2016 Series Editor Mary Anne
- Slides: 57
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
Objective 1: Design a Table Skills: Design a Table
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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 • Close the database and exit Access.
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. 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, 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 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. 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
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 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 ” • 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 “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 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 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 5: Establish table relationships
Exercise-2 • Task 5: Establish table relationships
Exercise-2 • Task 5: Establish table relationships
- Exploring microsoft office 2016
- Exploring microsoft office excel 2016 comprehensive
- Exploring microsoft office 2013 volume 1
- Exploring microsoft office 2013 volume 1
- Exploring microsoft office xp
- Microsoft official academic course microsoft word 2016
- Microsoft official academic course microsoft excel 2016
- Microsoft official academic course microsoft word 2016
- Microsoft office 2016 in practice
- Instanfeed
- Exploring series excel
- Forensics
- Ocls catalog
- Jill shively
- Sweetheart of song tra bong
- Dr mary ann lim abrahan
- Mary wollstonecraft mary a fiction
- Microsoft excel 2016 basics vocabulary
- Free threat modeling tools
- Microsoft excel 2016 basics vocabulary
- Office 2016 aktiválás
- Deshabilitar vista protegida office 2016
- Mary wanted to paint her office to add some new
- Weather observations website
- Maclaurin series vs taylor series
- Heisenberg 1925 paper
- Taylor series of composite functions
- Maclaurin series vs taylor series
- Ibm p series models
- Voltage shunt feedback amplifier forms
- Series aiding and series opposing
- Arithmetic series formula
- Microsoft startwarren theverge
- Excel merupakan program aplikasi
- Open xml productivity tool
- Silverlight ppt
- Ukf microsoft office
- Microsoft visio 2007
- Do microsoft office specialist certifications expire
- Microsoft office enterprise project management
- Lembar kerja program aplikasi pengolah angka adalah.
- History microsoft office
- Microsoft pdg
- Pdg microsoft
- Microsoft decision tree
- Boston university bursar office
- Microsoft project schedule tutorial
- Office n
- Kelompok microsoft office
- Ms project 2007
- Publisher 2007
- Microsoft dynamics gp compliance automation
- Office automation microsoft dynamics gp
- Drexel microsoft office
- Microsoft office
- Microsoft office enterprise project management
- Microsoft office live meeting 2007
- Microsoft office is an example of