New Perspectives on Microsoft Access 2016 Module 2: Building

New Perspectives on Microsoft Access 2016 Module 2: Building a Database and Defining Table Relationships 1

Objectives Session 2.1 Learn the guidelines for designing databases and setting field properties Create a table in Design view Define fields, set field properties, and specify a table’s primary key Modify the structure of a table Change the order of fields in Design view Add new fields in Design view Change the Format property for a field in Datasheet view Modify field properties in Design view 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 2

Objectives (Cont.) Session 2.2 Import data from an Excel worksheet Import an existing table structure Add fields to a table with the Data Type gallery Delete and rename fields Change the data type for a field in Design view Set the Default Value property for a field Import a text file Define a relationship between two tables 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 3

Guidelines for Designing Databases Case – Riverview Veterinary Care Center Database currently contains one table, named the Visit table User wants to track information about the clinic’s animals, their owners, and the invoices sent to them for services provided - This information includes such items as each owner’s name and address, animal information, and the amount and billing date for each invoice Create three new tables—named Billing, Owner, and Animal—to contain the additional data Kimberly wants to track After adding records to the tables, you will define the necessary relationships between the tables, and learn how to modify the fields 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 4

Guidelines for Designing Databases 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 5

Guidelines for Designing Databases Identify all the fields needed to produce the required information Organize each piece of data into its smallest useful part Group related fields into tables Determine each table’s primary key Include a common field in related tables Avoid data redundancy 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 6

Guidelines for Setting Field Properties Naming Fields and Objects You must name each field, table, and other object in an Access database Assigning Field Data Types Each field must have a data type - Data types are assigned automatically by Access or specifically by the table designer - The data type determines what field values you can enter for the field and what other properties the field will have 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 7

Guidelines for Setting Field Properties (Cont.) 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 8

Guidelines for Setting Field Properties (Cont.) Setting Field Sizes The Field Size property defines a field value’s maximum storage size for Short Text, Number, and AutoNumber fields only The other data types have no Field Size property because their storage size is either a fixed, predetermined amount or is determined automatically by the field value itself Setting the Caption Property for Fields The Caption property for a field specifies how the field name is displayed in database objects If you don’t set the Caption property, Access displays the field name as the column heading or label for a field 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 9

Creating a Table in Design View Creating a table in Design view involves entering the field names and defining the properties for the fields, specifying a primary key for the table, and then saving the table structure Defining Fields When you first create a table in Design view, the insertion point is located in the first row’s Field Name box, ready for you to begin defining the first field in the table Enter values for the Field Name, Data Type, and Description field properties, and then select values for all other field properties in the Field Properties pane These other properties will appear when you move to the first row’s Data Type box 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 10

Creating a Table in Design View (Cont.) 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 11

Creating a Table in Design View (Cont.) Specifying the Primary Key A primary key uniquely identifies each record in a table Access does not allow duplicate values in the primary key field When a primary key has been specified, Access forces you to enter a value for the primary key field in every record in the table (entity integrity) You can enter records in any order, but Access displays them by default in order of the primary key’s field values Access responds faster to your requests for specific records based on the primary key Saving the Table Structure The last step in creating a table is to name the table and save the table’s structure 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 12

Modifying the Structure of an Access Table Moving a Field in Design View To move a field, you use the mouse to drag it to a new location in the Table Design grid - You can move a field in Datasheet view by dragging its column heading to a new location, doing so rearranges only the display of the table’s fields; the table structure is not changed - To move a field permanently, move the field in Design view Adding a Field in Design View To add a new field between existing fields, you must insert a row Begin by selecting the row below where you want the new field to be inserted 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 13

Modifying the Structure of an Access Table 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 14

Modifying Field Properties Changing the Format Property in Datasheet View The Formatting group on the FIELDS tab in Datasheet view allows you to modify formatting for certain field types When you format a field, you change the way data is displayed, but not the actual values stored in the table Changing Properties in Design View Each of the Short Text fields has the default field size of 255, which is too large for the data contained in these fields 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 15

Adding Records to a New Table Adding Records to a New Table The Billing table design is complete and you would like to add records to the table so it will contain the invoice data Add records to a table in Datasheet view by typing the field values in the rows below the column headings for the fields 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 16

Adding Records to a New Table (Cont.) 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 17

Importing Data from an Excel Worksheet When data you want to add to an Access table exists in another file—like Word or Excel—you can bring the data from other files into Access in different ways Copy and paste the data from an open file Import the data, which is a process that allows you to copy the data from a source without having to open the source file 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 18

Importing Data from an Excel Worksheet (Cont.) 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 19

Creating a Table by Importing an Existing Table or Table Structure If another Access database contains a table—or even just the design, or structure, of a table—that you want to include in your database, you can import the table and any records it contains or import only the table structure into your database 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 20

Creating a Table by Importing an Existing Table or Table Structure 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 21

Adding Fields to a Table Using the Data Type Gallery The Data Type gallery (in the Add & Delete group on the FIELDS tab) allows you to add a group of related fields to a table at the same time, rather than adding each field to the table individually 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 22

Modifying the Structure of an Imported Table Deleting Fields from a Table Structure After you’ve created a table, you might need to delete one or more fields (which also deletes all the values for that field from the table) Before you delete a field, you should make sure that you want to do so and that you choose the correct field to delete Fields can be deleted in either Datasheet view or Design view Renaming Fields in Design View To match the design for the Owners table, you need to rename the State/Province and ZIP/Postal fields Fields can be renamed in Datasheet view or Design view 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 23

Modifying the Structure of an Imported Table Renaming Fields in Design View To match the design for the Owner table, you need to rename the State/Province and ZIP/Postal fields Fields can be renamed in Datasheet view or Design view Changing the Data Type for a Field in Design View All of the fields in the Patient table, except Birth Date, should be Short Text fields The table structure you imported specifies the Number data type for the Phone field —it should be Short Text The Data Type can be changed in Datasheet view or Design view 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 24

Setting the Default Value Property for a Field The Default Value property for a field specifies what value will appear, by default, for the field in each new record you add to a table 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 25

Adding Data to a Table by Importing a Text File Many ways to import data into an Access database Importing an Excel spreadsheet Created a new table by importing the structure of an existing table Import data contained in text files 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 26

Defining Table Relationships 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 27

Defining Table Relationships (Cont.) One-to-Many Relationships A one-to-many relationship exists between two tables when one record in the first table matches zero, one, or many records in the second table, and when one record in the second table matches at most one record in the first table Referential Integrity A set of rules that Access enforces to maintain consistency between related tables when you update data in a database 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 28

Defining Table Relationships (Cont.) Referential Integrity When you add a record to a related table, a matching record must already exist in the primary table, preventing the possibility of orphaned records If you attempt to change the value of the primary key in the primary table, Access prevents this change if matching records exist in a related table With the Cascade Update Related Fields option, Access permits the change in value to the primary key and changes the appropriate foreign key values in the related table If you attempt to delete a record in the primary table, Access prevents the deletion if matching records exist in a related table. However, if you choose the Cascade Delete Related Records option, Access deletes the record in the primary table and also deletes all records in related tables that have matching foreign key values 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 29