INSTRUCTOR-LED COURSEWARE Access 2016 – Level 2 © PCM Courseware, LLC. 6960 N. Ardara Ave., Glendale, WI 53209 Phone: 800-545-2729 http://www.pcmcourseware.com I N T R O D U C T I O N INSTRUCTOR-LED COURSEWARE Access 2016 – Level 2 © PCM Courseware, LLC. 6960 N. Ardara Ave., Glendale, WI 53209 Phone: 800-545-2729 http://www.pcmcourseware.com COPYRIGHT NOTICE AND LICENSE AGREEMENT © PCM Courseware, LLC. 2013 ALL RIGHTS RESERVED. This material is copyrighted and all rights are reserved by PCM Courseware, LLC. When you purchase this product, you are entitled unlimited use of purchased product in perpetuity. This product may be used by instructors only at a single site unless licenses were purchased for more than one site. However, instructors at a site may teach using the courseware at several locations; that is to say, instructors based out of a licensed physical location may travel and teach using the courseware at others locations. The number of sites eligible for use of the course materials will equal the number of site licenses purchased. You may copy and distribute the manual files, lesson files and lab files only within the confines of the specific site(s) of the license agreement. You may not under any circumstances, distribute, rent, sell or lease the manual, its documentation, the training files, or any copies thereof, to third parties. If the purchaser has more than one physical training location and wishes to use the courseware at these locations (branches in different cities, for example), then a licensed must be purchased for each additional location that contains instructors. Courseware may be customized and modified as the purchaser sees fit as long as the copyright information is clearly displayed within all documents. The purchaser may add their own name and logo to the printed manuals as long as the copyright information is present on all printed versions of the courseware. This courseware license may not be transferred, assigned, given, rented, leased or resold to any third party in any form. Introduction 2 I N T R O D U C T I O N Only printed copies of the courseware may be made available to students. Under no circumstances may the source Microsoft Word courseware files be made available on a network or the Internet that is accessible by the general public. It is the responsibility of the Purchaser to print out copies of the courseware. PCM Courseware, LLC will in no way be held responsible for inadequate printing facilities at the Purchaser’s site, resulting in the inability to print out the courseware. In such cases that the original source courseware files or training files are corrupted, PCM Courseware, LLC will replace any corrupted training files. Passwords necessary to access the courseware or download courseware from the PCM Courseware, LLC Web site must not be disclosed to any third party in any form. The purchaser may not make available any courseware to those who have not attended a training class at the purchaser’s licensed site. Under no circumstances may printed or electronic copies of our courseware be resold. If purchaser wishes to recoup their costs for copying the manuals, they may increase the price of the course to include manual copying costs. However, students may not be individually charged for the printed manuals. Manuals may not be placed for sale in any public location such as a bookstore or any other retail establishment. Passwords necessary to access the courseware or download courseware from the PCM Courseware, LLC Web site must not be disclosed to any third party in any form. Any student who has attended a training class in which PCM Courseware training materials were used may keep one copy of the printed training manual and any accompanying exercise and lab files for personal use only. PCM Courseware, LLC. reserves the right to revise this manual and its files and make changes from time to time in their content without notice. This license entitles the purchaser of the Entire Courseware Library to receive any new courseware or any updates to existing courseware produced within one (1) year of the purchase date via the PCM Courseware, LLC Web site. PCM Courseware, LLC will not be held responsible for an inadequate Internet Connection at the purchaser’s location resulting in an inability of download any new courseware. The purchaser’s remedy for problems or inconveniences encountered from the use of the training manual or its related training files shall be limited to the refund of the price paid for this courseware. PCM Courseware, LLC. shall not be liable to the purchaser or any other person with respect to any liability, loss or damage caused, directly or indirectly, by use of this courseware or the related training files. Please inform PCM Courseware, LLC of any errors or omissions in any of the courseware materials. While every genuine effort has been made to ensure the accuracy of the material, PCM Courseware, LLC. makes no warranty, express or implied, with respect to the correctness, reliability and freedom from error of the manual or the related training files. Data used in this manual and its training files are fictitious. Any reference to actual persons or companies is entirely coincidental. I N T R O D U C T I O N Table of Contents TABLE OF CONTENTS .......................................................................................................................4 ACCESS 2016 LEVEL 2 – INTRODUCTION .........................................................................................6 COURSE REQUIREMENTS ...............................................................................................................6 COMPONENTS OF THE MANUAL ....................................................................................................7 TRAINING FILES ............................................................................................................................8 WHAT’S NEW IN ACCESS 2016? ....................................................................................................9 LESSON 1 - DATABASE RELATIONSHIPS ..................................................................................10 1.1 A LOOK AT RELATIONSHIPS .............................................................................................11 1.2 CREATING A ONE-TO-ONE RELATIONSHIP .......................................................................13 1.3 CREATING A ONE-TO-MANY RELATIONSHIP ....................................................................18 1.4 CREATING A MANY-TO-MANY RELATIONSHIP.................................................................22 1.5 ENFORCING REFERENTIAL INTEGRITY..............................................................................26 1.6 CASCADE UPDATE RELATED FIELDS ................................................................................31 1.7 CASCADE DELETE RELATED RECORDS ............................................................................35 1.8 CREATING & PRINTING A RELATIONSHIP REPORT ............................................................39 LESSON SUMMARY – DATABASE RELATIONSHIPS .......................................................................42 LESSON 1 QUIZ............................................................................................................................44 LAB 1 – ON YOUR OWN .........................................................................................................46 LESSON 2 - WORKING WITH TABLES ......................................................................................47 2.1 SETTING VALIDATION RULES ...........................................................................................48 2.2 FORMATTING FIELDS........................................................................................................53 2.3 INDEXING FIELDS .............................................................................................................58 2.4 REQUIRING DATA ENTRY .................................................................................................61 2.5 CREATING AN INPUT MASK ..............................................................................................63 2.6 CREATING A LOOKUP FIELD .............................................................................................68 2.7 CREATING A VALUE LIST .................................................................................................74 2.8 MODIFYING A VALUE LIST ...............................................................................................78 2.9 CREATING CALCULATED FIELDS ......................................................................................80 2.10 CREATING MULTIPLE PRIMARY KEYS ..........................................................................83 2.11 CREATING MULTIPLE FIELD VALUES ............................................................................86 LESSON SUMMARY – WORKING WITH TABLES ............................................................................90 LESSON 2 QUIZ............................................................................................................................92 LAB 2 – ON YOUR OWN .........................................................................................................94 LESSON 3 3.1 3.2 3.3 3.4 3.5 3.6 3.7 WORKING WITH QUERIES.....................................................................................95 CREATING MULTI-TABLE QUERIES ..................................................................................96 USING CALCULATIONS IN QUERIES ..................................................................................99 CHANGING QUERY PROPERTIES .....................................................................................102 WORKING WITH THE EXPRESSION BUILDER ...................................................................105 CREATING A TOTALS QUERY .........................................................................................112 CREATING A PARAMETER QUERY ..................................................................................116 CREATING A FIND DUPLICATES QUERY .........................................................................120 Introduction 4 I N T R O D U C T I O N 3.8 CREATING A FIND UNMATCHED RECORDS QUERY.........................................................124 3.9 MODIFYING QUERY JOINS ..............................................................................................128 LESSON SUMMARY – WORKING WITH QUERIES ........................................................................132 LESSON 3 QUIZ..........................................................................................................................134 LAB 3 – ON YOUR OWN .......................................................................................................136 LESSON 4 - WORKING WITH FORMS......................................................................................138 4.1 ADDING HEADERS AND FOOTERS ...................................................................................139 4.2 ADDING CONTROLS TO A FORM .....................................................................................145 4.3 MOVING AND SIZING CONTROLS ....................................................................................148 4.4 CREATING A CALCULATED CONTROL ............................................................................151 4.5 CHANGING CONTROL PROPERTIES .................................................................................155 4.6 CHANGING FORM PROPERTIES .......................................................................................162 4.7 CHANGING THE TAB ORDER...........................................................................................166 4.8 ADDING A LOOKUP CONTROL ........................................................................................170 4.9 INSERTING GRAPHICS .....................................................................................................178 4.10 CREATING A SUBFORM................................................................................................183 LESSON SUMMARY – WORKING WITH FORMS ...........................................................................188 LESSON 4 QUIZ..........................................................................................................................190 LAB 4 – ON YOUR OWN .......................................................................................................192 LESSON 5 - WORKING WITH REPORTS ..................................................................................194 5.1 WORKING WITH REPORT SECTIONS ................................................................................195 5.2 ADDING CONTROLS TO A REPORT ..................................................................................199 5.3 CHANGING CONTROL PROPERTIES .................................................................................203 5.4 CREATING A CALCULATED CONTROL ............................................................................207 5.5 CHANGING A CONTROL’S DATA SOURCE .......................................................................215 5.6 CHANGING A REPORT’S DATA SOURCE ..........................................................................218 5.7 SORTING AND GROUPING DATA .....................................................................................221 5.8 CHANGING REPORT SECTION PROPERTIES .....................................................................227 5.9 INSERTING GRAPHICS .....................................................................................................230 5.10 APPLYING A THEME TO A REPORT ..............................................................................233 5.11 APPLYING CONDITIONAL FORMATTING ......................................................................236 LESSON SUMMARY – WORKING WITH REPORTS ........................................................................242 LESSON 5 QUIZ..........................................................................................................................244 LAB 5 – ON YOUR OWN .......................................................................................................246 CLASS PROJECT – TEDDY BEARS .................................................................................................248 INDEX ............................................................................................................................................250 I N T R O D U C T I O N Access 2016 Level 2 – Introduction Welcome to PCM Courseware! PCM Courseware is a distinctive, flexible system for an instructor-led environment that facilitates learning via auditory, visual and handson experiences by each student. The manual is broken down into several lessons with each lesson subdivided into several sections. Each section covers a particular skill or concept related to the main lesson topic. In each section you will find: 1. 2. 3. 4. 5. 6. A brief introduction to the section topic. Step-by-step “how to” instructions. A hands-on “Let’s Try It” exercise which students perform with the instructor. An independent “On Your Own” activity at the end of each lesson to identify any problem areas and to ensure that learning has taken place. A chapter summary at the end of the lesson, reviewing major concepts and topics discussed in the chapter. Chapter quiz to ensure that learning has taken place. Rather than having to sift through blocks of paragraphs of written text, the introductions are brief and easy to understand, illustrated with diagrams, lists, tables and screen shots to aid in comprehension and retention. The step-by-step format of the manual enables for quick scanning by the instructor during teaching time and the ability to pull out the main points quickly without having to filter the desired information from chucks of text. Course Requirements It is assumed that the student has a fundamental understanding of the Windows operating system and how to maneuver with a mouse. Students should be familiar with the concepts taught in the Access 2016 Level 1 course. A full installation of Microsoft Access 2016 should be available on each desktop, with a fresh installation strongly encouraged. Introduction 6 I N T R O D U C T I O N Components of the Manual The Access 2016 manual consists of the following components: A Table of Contents To allow the students to quickly find desired concepts Introduction Discussion of manual components, course requirements, courseware philosophy and training lab set. Lessons The lessons are the manual chapters, each of which is composed of several sections relating to the lesson topic or skill. Sections Each section begins with a brief introduction to the section topic and is followed by step-by-step instructions on how the student is to accomplish a particular task. The students then perform the task with the instructor in a “Let’s Do It!” exercise. Each step in the Let’s Do It exercise provides the How (step-by-step) and Why (the reason for performing the step) of each phase necessary to accomplish the task. Lesson Summary The sections concepts are summarized in sequential order in the Lesson Summary section, allowing for quick review. Labs Each section concludes with an independent “On Your Own” exercise called a Lab. The Lab gives the opportunity for the student to practice what he/she has learned and to discover any problem areas with the topic in the section. Each lab covers the skills taught in that particular lesson (chapter). Chapter Summary Each section concludes with a “Chapter Summary” which briefly reviews all of the topics discussed in the section. Chapter Quiz Each section concludes with an independent “Chapter Quiz” to test the level of learning that has taken place. The quiz is in multiple choice and short answer format and can be done in class together or as an end of chapter test. Class Project The course concludes with an independent “Class Project” to test the level of learning that has taken place. In this project, the student utilizes skills learned throughout the class. Index Allows students to quickly find desired concepts. I N T R O D U C T I O N Training Files Each PCM Courseware course comes with a set of Lesson Files and Lab Files designed to employ real-world situations and examples. The Lesson Files are designed to be used in the Let’s Do It exercises that the students perform with the instructor. The Lab Files are to be used for the On Your Own exercises at the end of each Lesson. Both the lesson files and lab files should be stored in the folder named: Lesson Files on each student’s desktop. Introduction 8 I N T R O D U C T I O N What’s New in Access 2016? What’s New Description of Feature App-Based Focus You can create an app in Access, upload it to SharePoint where it can then be accessed by anyone you like. Entering data accurately is much easier and less error prone with the new AutoComplete control provides drop-down lists and recommendations when you begin typing that makes entering data easier and more accurate. You can store your data on the cloud if you have SharePoint on 365. Allows you to save your database files to your Microsoft cloud account. Saving a database to SharePoint will create a full SQL Server to store the data. Access 2016 includes a set of new and professionally designed templates. New touch view suited especially for tablets and smartphones. A simpler, easier to use design. AutoComplete Control Cloud Hosting Skydrive SQL Backend Templates Touch Mode User Interface L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S 1 Lesson Lesson 1 - Database Relationships Lesson Topics: 1.1 A Look at Relationships 1.2 Creating a One-to-One Relationship 1.3 Creating a One-to-Many Relationship 1.4 Creating a Many-to-Many Relationship 1.5 Enforcing Referential Integrity 1.6 Cascade Update Related Fields 1.7 Cascade Delete Related Records 1.8 Creating and Printing a Relationship Report Access 2016 Level 2 10 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S 1.1 A Look at Relationships In lesson, you will learn about database relationships. T he power of a relational database lies in its ability to relate records from one table to records in another table. You relate records between two tables by creating a Relationship. A relationship is a way of formally defining how two tables are related to each other by telling the database on which fields they are joined. Relationships allow you to bring data together from the related tables. A relationship works by matching data in key fields – usually, a field with the same name in both tables. These matching key fields consist of the primary key from the parent table (which provides that each record’s value in that table must be unique) and the foreign key in the child table. In the example below, we have a customers table and an orders table. The two tables are joined on the CustomerID field. The CustomerID field in the customers table is set as the primary key, which is joined to the CustomerID field in the Orders table (the foreign key). Now, records for a customer with a particular CustomerID number will be related to any records in the order table where the CustomerID number is the same. Imagine we had a customer named Jane Smith whose CustomerID was 45. When entering any orders for Jane Smith in the orders table, Jane would be identified by her CustomerID number. We can then bring the data together, such as in a query. We might want to view a customer’s order information. To see this, we would use data from the customers and orders table. Database relationships fall into one of the three following categories: • • One-to-one. Each record has only one related record in the second table. One-to-many. Each record has one or more related records in the second table. © 2016 PCM Courseware, LLC 11 L E S S O N • 1 – D A T A B A S E R E L A T I O N S H I P S Many-to-many. Each record in one table may have many related records in the second table, and those related records may in turn have related records in the first table. We will examine each relationship type in subsequent lessons. Access 2016 Level 2 12 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S 1.2 Creating a One-to-One Relationship In lesson, you will learn how to create a one-to-one relationship. A One-to-One Relationship is a relationship where each record in the first table has one – and only one – related record in the second table. This is not a very common type of relationship but does exist nonetheless. For example, we may have an employees table that is accessible by many people. We may not wish to have employee salary information easily available so we place it in a separate table with the Employee ID and Salary fields. We then create a one-to-one relationship between the two tables. By setting the EmployeeID field as a primary key in both tables, we have thus created a one-to-one relationship. Remember, a field that is designated as a primary key field will not allow any duplicates in that field. Relationships are created in the Relationships Window. To display the Relationships Window, click the Database Tools tab on the Ribbon and then click the Relationships button on the Ribbon. In the example below, we have a customers table and a credit card table. For security reasons, we have decided to create a separate table to hold a customer’s default credit card information. As each customer will have only one default credit card on file, we create a one-to-one relationship. © 2016 PCM Courseware, LLC 13 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S To Create a One-to-One Relationship 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. Click the Database Tools tab on the Ribbon. Click the Relationships button on the Relationships group. Click the Tables tab if necessary on the Show Table dialog box. Select the first table to be added to the relationship. Click Add. Select the second table to be added to the relationship. Click Add. Click Close to close the Show Table dialog box. Click the primary key field in the first table and then drag to the primary key in the second table. Click Create in the Edit Relationship dialog box. Note: If a previous relationship has already been created, the “Show Table” dialog box will not appear. Click the Show Table on the Data Tools Ribbon to display the Show Table dialog box. Let’s Try It! What Why 1. Launches Microsoft Access and displays the Getting Started Pane. Open the Microsoft Access application. Access 2016 Level 2 14 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 2. Click the Open other Files in the left pane. Displays the Open pane. 3. Click the Browse icon in the center pane. Displays the Open dialog box. 4. Click Desktop on the left side of your screen. Displays the Desktop folder. 5. Double-click the Lesson Files folder in the right pane. Opens the Lesson Files folder and displays the files in that folder. 6. Select the Rodneys Video 1 file and then click Open. Opens the Rodneys Video 1 database. 7. Click the Database Tools tab on the Ribbon. Switches to Database Tools commands and tools. 8. Click the Relationships button on the Relationships group on the Ribbon as shown. Displays the Relationships window and then displays the Show Table dialog box. If there are already established relationships in your database, they will display in this window. 9. Select tblCustomers. Selects the first table we want to add to the Relationships window. 10. Click Add as shown below. © 2016 PCM Courseware, LLC Adds the selected table to the Relationships window. 15 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 11. Select tblCreditCard. Selects the second table we want to add to the Relationships Window. 12. Click Add. Adds the selected table to the Relationships Window. 13. Click Close. Closes the Show Table dialog box. 14. Click the CustomerID field in the tblCustomers table and then drag with your mouse to the CustomerID field in the tblCreditCard table. Release the mouse button. Creates a link between the two tables and then displays the Edit Relationships dialog box. 15. Click Create as shown below. Creates a relationship between the tblCustomers table and the tblCreditCard table, linking each table on the CustomerID field. Access 2016 Level 2 16 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 16. Click the Close button on the Relationship Window. Closes the Relationship Window. 17. Click Yes when asked if you wish to save the Relationships layout. Saves the relationship that we just created. © 2016 PCM Courseware, LLC 17 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S 1.3 Creating a One-to-Many Relationship In lesson, you will learn how to create a one-to-many relationship. A One-to-Many Relationship is a relationship where each record in the parent table has one or more related records in the child table. The one-to-many relationship is the most common type of relationship. A frequently used example is that of the Customer and Orders table. The Customer Table is the parent table and usually contains a Customer ID field which is set as a primary key. The Orders Table is the child table or the “many” side of the relationship. This table also contains a Customer ID field but it is not set as a primary key as a customer could place more than one order. This field is referred to as the foreign key. In the example below, the tblCustomers table is the “one” table as each customer is listed only once. The tblOrders table is the “many” table as a customer could place more than one order. To Create a One-to-Many Relationship 1. 2. 3. 4. 5. 6. 7. 8. Click the Database Tools tab on the Ribbon. Click the Relationships button on the Ribbon. Click the Tables tab if necessary on the Show Table dialog box. Select the first table to be added to the relationship. Click Add. Select the second table to be added to the relationship. Click Add. Click Close to close the Show Table dialog box. Access 2016 Level 2 18 L E S S O N 9. 10. 1 – D A T A B A S E R E L A T I O N S H I P S If necessary, click the primary key field in the parent table and then drag with your mouse to the foreign key in the child table (Access will most often automatically do this for you). Click Create in the Edit Relationship dialog box. Let’s Try It! What Why 1. Click the Database Tools tab on the Ribbon. Switches to Database Tools commands and tools. 2. Click the Relationships button on the Ribbon. Displays the Relationships window. As there are already established relationships in the database, the Show Table dialog box does not automatically display. 3. Click the Show Table button on the Relationships group as shown. Displays the Show Table dialog box. 4. Select tblOrders. Selects the table we want to add to the Relationships window. 5. Click Add. Adds the selected table to the Relationship window. 6. Click Close. Closes the Show Table dialog box. 7. Click the Title Bar for tblOrders and then drag the table so that it is underneath tblCustomers as shown below. Clicking the Title Bar of a table selects that table and then allows you to drag it to a different area of the Relationships Window. © 2016 PCM Courseware, LLC 19 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 8. Click the CustomerID field in the tblCustomers table and then hold down your mouse button. Selects the field in the Parent table. 9. Drag with your mouse button held down to the CustomerID field in the tblOrders table and then release the mouse button. Displays the Edit Relationships dialog box. 10. Click Create. Creates a one-to-many relationship from tblCustomers (the “one” table) to tblOrders (the “many” table), linking both tables on the CustomerID field. 11. Observe the Relationship Window as shown below. We now have a one-to-one relationship and a one-to-many relationship. Access 2016 Level 2 20 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 12. Click the Close icon on the Ribbon. Closes the Relationship Window. 13. Click Yes when asked if you wish to change the Relationships layout. Saves the relationship that we just created. © 2016 PCM Courseware, LLC 21 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S 1.4 Creating a Many-to-Many Relationship In lesson, you will learn how to create a many-to-many relationship. A Many-to-Many Relationship exists between a pair of tables if a single record in the first table can be related to one or more records in the second table, and a single record in the second table can be related to one or more records in the first table. The classic many-to-many example is the Students and Classes relationship – a student can take more than one class and a class is usually taken by more than one student. However, if you linked the Students and Classes table directly, you would receive a large amount of redundant data in your resultset. Inserting, Updating and Deleting data in this type of relationship can also be a problem. You solve the many-to-many relationship problem be creating an intermediary table (sometimes referred to as a junction table) that contains the primary keys from each of the two tables, thus creating a one-to-many relationship between each table and the intermediary table. For instance, in the Students and Classes example, we would create a linking table perhaps called StudentClasses, which would contain the StudentID field and the ClassID field as foreign keys. We would then create a one-to-many relationship between the Students table and StudentClasses table and another one-to-many relationship between the Classes table and the StudentClasses table. A Many-to-many relationship consisting of two One-to many-relationships with an Intermediary table (or linking table) Access 2016 Level 2 22 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S To Create a Many-to-Many Relationship 1. 2. 3. 4. 5. 6. 7 8. 9. 10. 11. 12. 13. Click the Database Tools tab on the Ribbon. Click the Relationships button on the Ribbon. Click the Tables tab if necessary on the Show Table dialog box. Select the first table to be added to the relationship. Click Add. Select the second table to be added to the relationship. Click Add. Select the Linking table (or intermediary table) to be added to the relationship (the linking table contains the primary key fields (as foreign keys) from the first and second tables). Click Close to close the Show Table dialog box. Click the primary key field in the first table and then drag with your mouse to the matching foreign key in the linking table. Click Create in the Edit Relationship dialog box. Click the primary key field in the second table and then drag with your mouse to the matching foreign key in the linking table. Click Create in the Edit Relationships dialog box. Let’s Try It! What Why 1. Click the Database Tools tab on the Ribbon. Switches to Database Tools commands and tools. 2. Click the Relationships button on the Ribbon. Displays the Relationships window. 3. Click the Show Table button on the Relationships group. Displays the Show Table dialog box. 4. Select tblProducts. Selects the table we want to add to the Relationship window. The first table, tblOrders has already been added to the Relationship window. 5. Click Add. Adds the selected table to the Relationships window. © 2016 PCM Courseware, LLC 23 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 6. Select tblOrderDetails. Selects the linking table we want to add to the Relationship window. There is a many-tomany relationship between Orders and Products (an order can contain more than one product and a product can be included in more than order). To solve this problem, we created an intermediary table, tblOrderDetails, which contains the ProductID field and the OrderID field. 7. Click Add. Adds the selected table to the Relationships window. 8. Click Close. Closes the Show Table dialog box. 9. Arrange your Relationships Window as shown below. Arranges the Relationship Window so we can easily establish our relationships. 10. Click the OrderID field in the tblOrders table and then hold your mouse button down. Selects the primary key field in the first table. 11. Drag to the OrderID field in the tblOrderDetails table and then release your mouse button. Creates a link from tblOrders to tblOrderDetails (our linking table) Access 2016 Level 2 24 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 12. Click Create. Creates a one-to-many relationship between the tblOrders table and the tblOrderDetails table, linking each table on the OrderID field. 13. Click the ProductID field in the tblProducts table and then hold your mouse button down. Selects the primary key field in the second table. 14. Drag to the ProductID field in the tblOrderDetails table and then release your mouse button. Creates a link from tblProducts to tblOrderDetails (our linking table) 15. Click Create. Your Relationship window should look similar to the one below. Creates a one-to-many relationship between the tblProducts table and the tblOrderDetails table, linking each table on the ProductID field. © 2016 PCM Courseware, LLC 25 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S 1.5 Enforcing Referential Integrity In lesson, you will learn how to enforce referential integrity in a relationship. R eferential Integrity is a system of rules that Access uses to ensure that relationships between records in related tables are valid, and that you don’t accidentally delete or change data in one table and not in the other. For example, referential integrity ensures that you cannot enter a record into the Orders table for a customer that does not exist in the Customers table. Once you have chosen to enforce referential integrity in your tables, your data is protected in the following ways: • • • You cannot enter a value in a foreign key field of a relationship if there is no matching value in the primary (or parent) table. For example, you cannot enter a record in the Orders table for a customer that does not exist in the Customers table. You cannot delete a record in the primary table if a related record exists in a matching table (unless you have checked the Cascade Delete Related Records option). For instance, you cannot delete a record for a customer in the Customers table if that customer has orders in the Orders table. You cannot change the value of the primary key field in the primary table if there are matching records in the related table (unless you have checked the Cascade Update Related Fields option). However, before you can enforce referential integrity, there are certain conditions that must be met. These are: Access 2016 Level 2 26 L E S S O N • • • 1 – D A T A B A S E R E L A T I O N S H I P S The matching field in the parent table must be a primary key or have a unique index. The related fields must have the same data type. The tables must reside in the same Access database To Enforce Referential Integrity 1. After creating the relationship link, check Enforce Referential Integrity in the Edit Relationships dialog box. If the relationship has already been created, double-click on the relationship link to display the Edit Relationships dialog box and then check the Enforce Referential Integrity checkbox. 2. Let’s Try It! What Why 1. Click the Show Table button on the Relationships group. Displays the Show Table dialog box. 2. Select tblShippers. Selects the table we want to add to the Relationship window. 3. Click Add. Adds the selected table to the Relationship window. 4. Click Close. Closes the Show Table dialog box. © 2016 PCM Courseware, LLC 27 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 5. Click the Shipper field in the tblOrders table and hold down your mouse button. Selects the field in the Parent Table. 6. Drag with your mouse button held down to the ShipperID field in the tblShippers table and then release your mouse button. Creates a link from tblOrders to tblShippers. 7. Click the Enforce Referential Integrity checkbox as shown below. Sets the option to enforce referential integrity. 8. Click Create. Creates a one-to-many relationship between the tblOrders table and the tblShippers table with referential integrity enforced. 9. Double-click the link line between tblCustomers and tblCreditCard as shown. Displays the Edit Relationships dialog box for that relationship. Access 2016 Level 2 28 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 10. Click the Enforce Referential Integrity checkbox. Enforces referential integrity for the relationship. 11. Click OK. Closes the Edit Relationships dialog box. 12. Double-click the link line between tblProducts and tblOrderDetails. Displays the Edit Relationships dialog box for that relationship. 13. Click the Enforce Referential Integrity checkbox. Enforces referential integrity for the relationship. 14. Click OK. Closes the Edit relationships dialog box. 15. Double-click the link line between tblCustomers and tblOrders. Displays the Edit Relationships dialog box for that relationship. 16. Click the Enforce Referential Integrity checkbox. Enforces referential integrity for the relationship. 17. Click OK. Closes the Edit relationships dialog box. 18. Click the Close icon on the Ribbon. Save any changes. Saves our relationship changes and closes the Relationship dialog box. 19. Double-click the tblOrders table. Opens tblOrders in Datasheet view. © 2016 PCM Courseware, LLC 29 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 20. Double-click in the Shipper field for the first record. Selects the value in the Shipper field. 21. Type: 11 Changes the value in the Shipper field to 11. 22. Press the down arrow key. Attempts to move off the record. An error message is displayed telling us that a related record is required in tblShippers. As there is no shipper ID of 11 in tblShippers, referential integrity is violated and Access will not allow us to save the record. 23. Cllick OK. Closes the error box and returns us to the shipper field. 24. Press the Delete key twice. Deletes the value in the Shipper field. 25. Type: 3 Enters a new value for the Shipper field. 26. Press the down arrow key. Moves off of the record. We did not receive an error message this time because a Shipper ID of 3 exists in tblShippers. 27. Click the Close button for tblOrders. Closes tblOrders. Access 2016 Level 2 30 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S 1.6 Cascade Update Related Fields In lesson, you will learn how to set the Cascade Update Related Fields option. O nce referential integrity is enforced, you may wish to set the Cascade Update Related Fields option in the Edit Relationships dialog box. With this option set, update options that would normally be prevented by referential integrity rules are allowed. Setting the Cascade Update Related Fields option specifies that any time you change the primary key of a record in the primary (or parent) table, Access will automatically update the primary key to the new value in all related records. For example, if you change a customer’s ID in the Customers table, the CustomerID field in the Orders table is automatically updated for every one of the customer’s orders. This prevents the relationship from being broken and the creation of orphaned records. To Set the Cascade Update Related Fields Option 1. 2. After creating the relationship link, check the Cascade Update Related Fields checkbox in the Edit Relationships dialog box. If the relationship has already been created, double-click the relationship link to display the Edit Relationships dialog box. © 2016 PCM Courseware, LLC 31 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S Let’s Try It! What Why 1. Double-click tblCustomers. Opens tblCustomers in datasheet view. 2. Double-click in the CustomerID field for the second record (Mary Nolan). Selects the CustomerID field for customer 2. 3. Type: 75. Changes the CustomerID for Mary Nolan to 75. 4. Press the down arrow key. Attempts to move off the record but we receive an error message that the record cannot be changed. Referential integrity forbids us from changing this record because there is a related record in tblOrders and tblCreditCard. 5. Click OK. Closes the error box. 6. Press the Esc key. Cancels the attempted change. 7. Click the Close button for tblCustomers. Closes tblCustomers. 8. Double-click tblCreditCard and observe the record for Mary Nolan. Opens tblCreditCard in Datasheet view. Note that the CustomerID for MaryNolan is 2. 9. Click the Close button for tblCreditCard. Closes tblCreditCard. 10. Click the Database Tools tab on the Ribbon. Access 2016 Level 2 Switches to Database Tools commands and tools. 32 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 11. Click the Relationships button on the Ribbon. Displays the Relationships window. 12. Double-click the link line between tblCustomers and tblCreditCard. Displays the Edit Relationships dialog box for that relationship. 13. Click the Cascade Update Related Fields checkbox as shown below. Sets the option to Cascade Update Related Fields for the relationship. 14. Click OK. Closes the Edit Relationships dialog box. 15. Double-click the link line between tblCustomers and tblOrders. Displays the Edit Relationships dialog box for that relationship. 16. Click the Cascade Update Related Fields checkbox. Sets the option to Cascade Update Related Fields for the relationship. 17. Click OK. Closes the Edit Relationships dialog box and enforces referential integrity. 18. Click the Close icon on the Ribbon. Save any changes. Saves our relationship changes and closes the Relationship Window. 19. Double-click tblCustomers. Opens tblCustomers in Datasheet view. © 2016 PCM Courseware, LLC 33 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 20. Double-click in the CustomerID field for the second record (Mary Nolan). Selects the CustomerID field for customer 2. 21. Type: 75. Changes the CustomerID for Mary Nolan to 75. 22. Press the down arrow key. This time, we were able to change the record as we have set the Cascade Update Related Records option. The related records will have also been updated. 23. Click the Close button for tblCustomers. Closes tblCustomers. 24. Double-click tblCreditCard. Opens tblCreditCard in datasheet view. Notice that the CustomerID for record 2 has been changed to 75. CustomerID of 2 changed to 75 25. Click the plus (+) symbol to the left of the 75. Displays the related record for Mary Nolan in the Customer table. 26. Press the Ctrl + W keystroke combination. Closes tblCreditCard. Access 2016 Level 2 34 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S 1.7 Cascade Delete Related Records In lesson, you will learn how to set the Cascade Delete Related Records option. O nce referential integrity is enforced, you may wish to set the Cascade Delete Related Records option in the Edit Relationships dialog box. With this option set, delete options that would normally be prevented by referential integrity rules are allowed. Setting the Cascade Delete Related Records option specifies that when you delete a record in the primary (or parent) table, all of the related records will be deleted as well. For example, if you delete a customer in the Customers table, all orders for that customer in the Orders table will automatically be deleted. This prevents the relationship from being broken and the creation of orphaned records. If this option is not set and you wished to delete a customer along with his or her orders, you would first need to delete all records from the orders table before being able to delete the customer. To Set the Cascade Delete Related Records Option 1. After creating the relationship link, check the Cascade Delete Related Records checkbox in the Edit Relationships dialog box. © 2016 PCM Courseware, LLC 35 L E S S O N 2. 1 – D A T A B A S E R E L A T I O N S H I P S If the relationship has already been created, double-click the relationship link to display the Edit Relationships dialog box. Then, check the Cascade Delete Related Records checkbox in the Edit Relationships dialog box. Let’s Try It! What Why 1. Double-click tblCustomers. Opens tblCustomers in Datasheet view. 2. Click the record selector for Mary Nolan, CustomerID 75 as shown below. Selects the record for Mary Nolan. 3. Press the Delete key. Attempts to delete the selected record. However, referential integrity forbids us from deleting this record because there is a related record in tblOrders and tblCreditCard. 4. Click OK. Closes the error box. 5. Click the Close button for tblCustomers. Closes tblCustomers. 6. Click the Database Tools tab on the Ribbon. Switches to Database Tools commands and tools. 7. Click the Relationships button. Displays the Relationships window. Access 2016 Level 2 36 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 8. Double-click the link line between tblCustomers and tblCreditCard. Displays the Edit Relationships dialog box for that relationship. 9. Click the Cascade Delete Related Records checkbox. Sets the Cascade Delete Related Records option for the relationship. 10. Click OK. Closes the Edit Relationships dialog box. 11. Double-click the link line between tblCustomers and tblOrders as shown. Displays the Edit Relationships dialog box for that relationship. 12. Click the Cascade Delete Related Records checkbox. Sets the Cascade Delete Related Records option for the relationship. 13. Click OK. Closes the Edit Relationships dialog box. 14. Click the Close icon on the Ribbon. Save any changes. Saves our relationship changes and closes the Relationship dialog box. 15. Double-click tblCustomers. Opens tblCustomers in Datasheet view. 16. Click the record selector for Mary Nolan, CustomerID 75. Selects the record for Mary Nolan. 17. Press the Delete key. We receive a message box telling us that if we delete this records, related records will be deleted as well. 18. Click Yes. Deletes the record for Mary Nolan as well as all related records in tblCreditCard and tblOrders. 19. Click the Close button for tblCustomers. Closes tblCustomers. © 2016 PCM Courseware, LLC 37 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 20. Double-click tblCreditCard. Opens tblCreditCard in datasheet view. Notice that the record for CustomerID 75 is gone. 21. Click the Close button for tblCreditCard. Closes tblCreditCard. 22. Double-click tblOrders. Opens tblOrders in Datasheet view. Notice that the only record in tblOrders, CustomerID 75, is gone. 23. Click the Close button for tblOrders. Closes tblOrders. Access 2016 Level 2 38 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S 1.8 Creating & Printing a Relationship Report In lesson, you will learn how to create and print a relationship report. O nce your relationships are established, you can create and print a report which illustrates the relationships in your database. The report is displayed in Print Preview, allowing you then to either send a copy to the printer or save the report as a PDF, XPS, Word, text or HTML document. When in Print Preview, you can also make additional changes to your report by using the tools on the contextual Print Preview Ribbon. For instance, you can change the paper size, paper orientation, and change the page margins. To Create and Print a Relationship Report 1. 2. 3. Click the Database Tools tab on the Ribbon. Click the Relationships button on the Ribbon. Click the Relationship Report button on the Tools group of the contextual Design Ribbon. The relationship report will display in Print Preview mode. © 2016 PCM Courseware, LLC 39 L E S S O N 4. 1 – D A T A B A S E R E L A T I O N S H I P S To send your relationship report to the printer, click the Print button and specify any desired settings. To modify any additional relationship report options, make your selections on the Print Preview Ribbon. Click the Close Print Preview button to display the relationship report in Design View. To save your relationship report, click the Save button on the Quick Access toolbar and enter a name for your report. Click OK. 5. 6. 7. 8. Let’s Try It! What Why 1. Click the Database Tools tab on the Ribbon. Switches to Database Tools commands and tools. 2. Click the Relationships button on the Ribbon. Displays the Relationships window. 3. Click the Relationship Report button on the Tools group on the contextual Design Ribbon as shown below. Displays the report in Print Preview. 4. Click the PDF or XPS button on the Data group on the Ribbon. Displays the Publish as PDF or XPS dialog box, allowing us to export our report to PDF or XPS format. 5. Ensure that PDF is displayed in the Save as Type box. Specifies that we want to export our report in PDF format. Access 2016 Level 2 40 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S What Why 6. Click Desktop on the left side of your screen. Displays the Desktop folder. 7. Double-click the Lesson Files folder in the right pane. Opens the Lesson Files folder and displays the files in that folder. 8. In the File Name box, type: Relationship_Report as shown below. Specifies a name for our relationship report. Type: “Relationship_Report” 9. Uncheck the Open File after publishing checkbox if it is checked. Sets the option not to open the file after it is exported. 10. Click Publish. Exports our relationship report to PDF format and displays the Export PDF dialog box. 11. Click Close. Closes the Export PDF dialog box. 12. Click the Close button on the Relationships Report window. Displays a dialog box asking you if you wish to save the relationship report. 13. Click No. Closes the report without saving it in the database. 14. Click the File tab on the Ribbon and then click Close from the File Options menu. Closes the database. © 2016 PCM Courseware, LLC 41 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S Lesson Summary – Database Relationships ➢ In this Lesson, you learned that a relationship is a way of formally defining how tables are related to each other. You learned that there are three categories of relationships: One-to-one, One-to-many and Many-to-many. ➢ Then, you learned how to create a One-to-one relationship where each table has only one related record by clicking the Relationships button on the Database Tools Ribbon and dragging from the key field of the first table to the key field of the second table. ➢ Next, you learned how to create a One-to-many relationship, where each record in the parent table has one or more related records in the child table. Drag from the key field of the parent table to the common “many” field (the foreign key) in the child table. ➢ Next, you learned how to create a Many-to-many relationship, where a single record in the first table can be related to one or more records in the second table, and a single record in the second table can be related to one or more records in the first table. You learned that in order to create a Many-to-many relationship, you need to add an intermediary table which contains the primary keys from each of the two tables. ➢ Next, you learned that Referential Integrity is a system of rules that Access uses to ensure that relationships between records in related tables are valid, and that you don’t accidentally delete or change data in one table and not in the other. To enforce Referential Integrity, double-click the Relationship line between the tables and click the Enforce Referential Integrity checkbox. ➢ Next, you learned how to set the Cascade Update Related Fields option, which specifies that any time you change the primary key of a record in the primary (or parent) table, Access will automatically update the primary key to the new value in all related records. To set this option, double-click the Relationship line between the tables and click the Cascade Update Related Fields checkbox. ➢ Next, you learned how to set the Cascade Delete Related Records option, which specifies that when you delete a record in the primary (or parent) table, all of the related records will be deleted as well. To set this option, doubleclick the Relationship line between the tables and click the Cascade Delete Related Records checkbox. ➢ Lastly, you learned how to create and print a relationship report, which illustrates the relationships in your database. Click the Relationship Report Access 2016 Level 2 42 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S button on the Tools group of the contextual Design Ribbon to display the relationship report in Print Preview. © 2016 PCM Courseware, LLC 43 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S Lesson 1 Quiz 1. In a One-to-Many Relationship, A. Each record in the first table has only one related record in the second table. B. Each record in the second table has many related records in the first table. C. Each record in the first table has one or more related records in the second table. D. Each record in one table may have many related records in the second table, and those related records may in turn have related records in the first table. 2. You have one table that contains a list of employees and another table that contains a list of the employees’ social security numbers. What type of relationship would you create? 3. The Relationships button can be found under what Ribbon tab? A. Relationships B. Database Tools C. Create D. Home 4. You want to add a new table to an existing relationship. How do you accomplish this? A. Click the Add Table button on the contextual Design Ribbon, click the table you want to add and then click Add. B. Click the Insert Table button on the contextual Design Ribbon, click the table you want to add and then click Add. C. Click the Show Table button on the contextual Design Ribbon, click the table you want to add and then click Add. D. Double-click the table you want to add in the Navigation Bar and then click the Add button on the Home Ribbon. 5. A frequent example of a ____________________________ (fill in the blank) relationship is the Customers and Orders table. 6. A set of rules that Access uses to ensure that the relationships between records and related tables is valid is called: A. Referential Integrity B. Cascading Records C. Primary Key Criteria D. Foreign Key Integrity Access 2016 Level 2 44 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S 7. A Primary Key can have duplicate values but a foreign key cannot. A. True B. False 8. You want to be able to change the customer number in a Primary Key field and have the customer number in all related fields update as well. What option would you have to set to be able to do this? A. Referential Integrity B. Cascade Delete Related Records C. Cascade Update Integrity D. Cascade Update Related Fields 9. When creating a Many-to-Many relationship between two tables, you need to add a(n) _________________ table that contains the Primary key fields from both tables. (fill in the blank) 10. How can you display the Edit Relationships dialog box? 11. You have a customer and orders table with the Cascade Delete Related Records option set. What will occur if you delete a customer from the Customers table? A. Access will not allow you to delete a customer if there are orders. B. The records in the orders table will then be orphaned and you will need to delete them manually. C. The related records in the orders table will be deleted as well. D. The related records in the orders table will be assigned a new customer number. 12. Which is NOT a format to which you can export a Relationship Report? A. Word B. PDF C. Text File D. PowerPoint © 2016 PCM Courseware, LLC 45 L E S S O N 1 – D A T A B A S E R E L A T I O N S H I P S LAB 1 – ON YOUR OWN 1. Open the Lab1 database in the Lesson Files folder. 2. Create a relationship between the Students table and the StudentClasses table. Link the two tables on the StudentID field. Close the Edit Relationships dialog box. 3. Add the Classes table to the Relationship window. 4. If necessary, create a relationship between the Classes table and StudentClasses table (Access may have automatically done this for you). Link the two tables on the ClassID field. Enforce referential integrity and check the Cascade Update Related Fields and Cascade Delete Related Records checkboxes. Click OK. 5. Double-click the relationship link between the Students table and the StudentClasses table. Enforce referential integrity and check the Cascade Update Related Fields and Cascade Delete Related Records checkboxes. 6. Close the Relationships Window and save any changes. 7. Open the Students table. Change the StudentID of Monica Fielen to 80000005. Close the Students table. 8. Open the StudentClasses table. Can you find the record for Monica Fielen? Close the StudentClasses table. 9. Create a relationship report and export it in PDF format to the Lesson Files folder. Name the report: My_Lab_1 10. Close the Lab1 database. Access 2016 Level 2 46 2 Lesson L E S S O N 2 – W O R K I N G W I T H T A B L E S Lesson 2 - Working with Tables Lesson Topic 2.1 Setting Validation Rules 2.2 Formatting Fields 2.3 Indexing Fields 2.4 Requiring Data Entry 2.5 Creating an Input Mask 2.6 Creating a Lookup Field 2.7 Creating a Value List 2.8 Modifying a Value List 2.9 Creating a Calculated Field 2.10 Creating Multiple Primary Keys 2.11 Creating Multiple Field Values © 2016 PCM Courseware, LLC 47 L E S S O N 2 – W O R K I N G W I T H T A B L E S 2.1 Setting Validation Rules In this lesson, you will learn how to set validation rules for your data. T o ensure that users enter valid data in a field, you can set a validation rule for data entry. A validation rule is a property that defines valid input entries for a field in a table. For example, if you started your business on February 15, 2013, you could set a validation rule for the order date field to be >=2/15/2013. If the data entered does not meet the requirements of the validation rule, the user receives an error message. You can even customize the error message the user receives by typing in the desired error message in the validation text box in Table Design view. You can either type the validation rule directly in the Validation Rule box under field properties or click the build button to the right of the property. The build button launches the expression builder, an Access tool that allows you to create an expression by selecting common functions, constants and operators from the expression window. To Set a Validation Rule 1. 2. Open the table in Design View that contains the field you want to restrict. Click anywhere in the row of the field for which you want to set a validation rule. Access 2016 Level 2 48 L E S S O N 3. 4. 2 – W O R K I N G W I T H T A B L E S Type in the expression in the Validation Rule box under Field Properties. If desired, type in a custom error message in the Validation Text box under Field Properties. Validation Rule Examples Validation Rule Expression Description >=50 Entry must be greater than or equal to 50 “MI” or “WI” Entry must be either MI or WI Between 5/1/2009 And 6/30/2014 Date must be between 5/1/2009 and 6/30/2014 >5/1/2013 Date must be greater than 5/1/2013 =Date() Entry must be today’s date Let’s Try It! What Why 1. Click the File tab and then click Open from Backstage view. Displays the Open dialog box. 2. Click the Browse icon in the center pane. Displays the Open dialog box. 3. Click Desktop on the left side of your screen. Displays the Desktop folder. 4. Double-click the Lesson Files folder in the right pane. Opens the Lesson Files folder and displays the files in that folder. 5. Select the Rodneys Video 2 file and then click Open. Opens the Rodneys Video 2 database. 6. Right-click on tblOrders in the Navigation Pane and then click the Design View. Displays tblOrders in Design view. © 2016 PCM Courseware, LLC 49 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 7. Click in the Field Name column for the OrderDate field as shown below. Selects the OrderDate Field. 8. Under Field Properties, click in the Validation Rule box and then type: >1/1/2001 as shown below. Sets a validation rule for the OrderDate field that the date must be greater than January 1, 2001. 9. In the Validation Text box, type: Date must be greater than 1/1/2001! as shown below. Enters a custom error message that the user receives if the validation rule is violated. Access 2016 Level 2 50 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 10. Click the Save button on the Quick Access Toolbar. Saves the design changes. 11. Click Yes when the message box appears. Access asks us if it should check to ensure that existing data in our table does not violate our new validation rule. 12. Click the View icon on the Ribbon. Switches to Datasheet view. 13. Click in the new row under CustomerID and type: 13. Creates a new row and enters a value of 13 for the CustomerID. 14. Press the Tab key. Moves to the next field. 15. Type: 6/25/2000 Enters a value for the OrderDate field. 16. Press Tab. As the data we entered in the OrderDate field violates the validation rule we set, our custom error message is displayed. 17. Click OK. Closes the error message box and returns us to the OrderDate field so that we can enter valid data. © 2016 PCM Courseware, LLC 51 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 18. Press the Backspace key once and then type: 2. Changes the year to 2002. 19. Press Tab. Moves to the next field. Our validation rule is no longer violated. 20. Enter the rest of the data for the current record as follows. Press Tab to move from one field to the next. Enters the data for the remaining fields of the current record. Ship Date: Shipper: Tax Rate: Freight Charge: Access 2016 Level 2 7/1/02 3 .06 $4.95 52 L E S S O N 2 – W O R K I N G W I T H T A B L E S 2.2 Formatting Fields In this lesson, you will learn how to modify the Format property of a field. A nother field property that you will find useful is the Format property. The format property is used to change how the data is displayed on your screen and in printed form. Access has several pre-defined formats that you can use to change the appearance of your data. Each data type in Access has its own set of format property settings. For example, you can format a date/time field so that it displays the date as Thursday, August 28, 2015 or as 8/28/2015. There may be times when the pre-defined formats are not sufficient for your needs. Luckily, there are a wide variety of custom formats that you can use. Custom formats are formatting symbols that are entered manually in the format field rather than choosing a pre-defined format from a list. For instance, you may want a date field to be displayed as Wed May 01. To accomplish this, you would enter: ddd mmm dd in the format field. Or to display your date in European format with the month preceding the date, enter: dd/mm/yyyy. © 2016 PCM Courseware, LLC 53 L E S S O N 2 – W O R K I N G W I T H T A B L E S To Change the Formatting of Fields 1. 2. 3. Open the table in Design View that contains the field you wish to format. Click anywhere in the row of the field to be formatted. To use a pre-defined format, click in the Format box under Field Properties, click the arrow on the right of the box and then select a pre-defined format from the drop-down list. To use a custom format, type the desired formatting characters directly into the Format box. 4. Let’s Try It! What Why 1. Click the View button on the Ribbon. Switches to Design View. 2. Click in the Field Name column for the OrderDate field. Displays the field properties for the OrderDate field. 3. Under Field Properties, click in the Format box. Activates the Format box for the OrderDate field. 4. Click the arrow on the right of the Format box and then select Long Date from the drop-down list as shown below. Changes the format of the OrderDate field to Long Date. Access 2016 Level 2 54 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 5. Click the Property Update Options box as shown below and select “Update Format everywhere OrderDate is used” as shown below. The Property Update Options box allows you the option of automatically updating the properties of any controls in all forms or reports that are bound to the field in a table when that field is updated in the table. This feature is known as “Propagating Field Properties.” When a change is made to a field in a table, the Property Update Options Box is displayed. 6. Click OK. As there were no controls in any forms or reports bound to the OrderDate field, Access informs us that no fields needed to be updated. 7. Click in the Field Name column for the DateShipped field. Displays the field properties for the DateShipped field. 8. Under Field Properties, doubleclick in the Format box. Selects the current format setting for the DateShipped field. © 2016 PCM Courseware, LLC 55 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 9. Enters a custom format of: Monday Jul 23 Type: dddd mmm dd 10. Click the Save button on the Quick Access Toolbar. Saves the design changes. 11. Click the View button and then observe the OrderDate and DateShipped fields. Widen the columns if needed in order to view all of the data. The formatting of our data has changed based on what we entered in the Format box. 12. Click the Close button for tblOrders. Save changes if prompted. Closes tblOrders. 13. Double-click tblCustomers and then click the View button. Displays tblCustomers in Design View. 14. Click in the Field Name column for the City field. Displays the field properties for the City field. 15. Under Field Properties, click in the Format box and then type: > Enters a custom text format that will display all data in the field in upper case. 16. Press the Down Arrow key. Moves to the next field property and displays the Property Update Options Box. 17. Click the Property Update Options box and select “Update Format Everywhere City is used.” Observe the Update Properties dialog box as shown below. Displays the Update Properties dialog box, listing all form and report objects where the field is used. Here, you have to option of updated individual objects or all objects at once. Access 2016 Level 2 56 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 18. Click Yes. Updates the properties of the City field in the rptCustomerList to the new format. 19. Click the Save button. Saves the design changes. 20. Click the View button and observe the City field. All data in the City field is displayed in uppercase. 21. Press the Ctrl + W keystroke combination. Closes tblCustomers. © 2016 PCM Courseware, LLC 57 L E S S O N 2 – W O R K I N G W I T H T A B L E S 2.3 Indexing Fields In this lesson, you will learn how to index a field in a table. I ndexes on a field in a table enable Access to find and sort records more quickly. It is a good idea to index fields in which you frequently search for data or fields that you sort by in queries. This will substantially speed up your searches and queries, especially when your database becomes large. However, be careful not to index too many fields as this can have the opposite effect and will actually slow down your searching and sorting. All data types can be indexed except for OLE, calculated and hyperlink fields. Primary Key fields are automatically indexed with no duplicates allowed. There are three index options from which to choose: • • • No (not indexed) Yes (Duplicates OK) Yes (No Duplicates) A good example of a situation where would use the Yes (Duplicates OK) option, is setting an index for a zip code, as searching for data in this field or sorting by this field is common. However, as you may possibly have entries for people who share the same zip code, you would need to allow duplicates entries in that field. After you create your indexes, you can view or edit them by clicking the Indexes button on the Show/Hide group of the Ribbon. From here, you can add new indexes as well as change the name of your indexes and the sort order of your indexes. Access 2016 Level 2 58 L E S S O N 2 – W O R K I N G W I T H T A B L E S To Index a Field 1. 2. 3. 4. 5. Select the table that contains the field you wish to index. Switch to Design View. Click anywhere in the row of the field to be indexed. Under Field Properties, click in the Indexed box. Click the arrow in the Indexed box and then choose the desired Index option from the drop-down list. To view or edit indexes, click the Indexes button on the Show/Hide group and make any desired changes. 6. Let’s Try It! What Why 1. Double-click tblCustomers and then click the View button. Displays tblCustomers in Design View. 2. Click in the Field Name column for the Zip field. Displays the field properties for the Zip field. 3. Under Field Properties, click in the Indexed box. Sets the insertion point in the Indexed field property for the Zip field. 4. Click the arrow in the Indexed box and then select Yes (Duplicates OK) as shown below. Indexes the Zip field and selects the Duplicates OK option. 5. Click in the Field Name column for the LastName field. Displays the field properties for the LastName field. As Last Name is a common field to search, we will index this field as well. 6. Under Field Properties, click in the Indexed box. Sets the insertion point in the Indexed field property for the LastName field. © 2016 PCM Courseware, LLC 59 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 7. Click the arrow in the Indexed box and then select Yes (Duplicates OK). Indexes the LastName field and then selects the Duplicates OK option. 8. Click the Indexes button on the Show/Hide group of the Ribbon as shown below. Displays the Indexes dialog box. 9. Double-click the Zip field in the Index Name column and type: PostalCode as shown below. Provides a different name for the index. Note that the field name remains the same in the table. 10. Click the Close button on the Indexes dialog box. Closes the Indexes dialog box. 11. Press the Ctrl + S keystroke combination. Saves our design changes. Access 2016 Level 2 60 L E S S O N 2 – W O R K I N G W I T H T A B L E S 2.4 Requiring Data Entry In this lesson, you will learn how to set the Required field property. A nother common field property is the Required property. You can use the Required property to specify whether a value is required in a particular field. If the property is set to Yes, the user must enter a value in a field. This is helpful to ensure that essential data is not left out of a record. For example, you might want to make sure that the Last Name field in a Customer Table is never left blank. To Set the Required Property 1. 2. 3. 4. Open the table in Design View that contains the field you wish to modify. Click anywhere in the row of the field to be changed. Under Field Properties, click in the Required box. Click the drop-down arrow and then choose either Yes or No. © 2016 PCM Courseware, LLC 61 L E S S O N 2 – W O R K I N G W I T H T A B L E S Let’s Try It! What Why 1. Click in the Field Name column for the LastName field. Displays the field properties for the LastName field. 2. Under Field Properties, click in the Required box. Activates the Required field property box for the LastName field. 3. Click the arrow in the Required box and select Yes. Sets the property of the LastName field to require a value. 4. Click the Save icon on the Quick Access toolbar. A message box appears asking us if we wish to test the existing data with the new data integrity rules. 5. Click Yes. Tests the data integrity rules and saves our design changes. Access 2016 Level 2 62 L E S S O N 2 – W O R K I N G W I T H T A B L E S 2.5 Creating an Input Mask In this lesson, you will learn how to create an Input Mask for a field. Y ou can use the Input Mask property (for text or date/time data types only) to ensure that data gets entered in the correct format. For instance, you might want the phone number field to be in the format: (000) 000-0000. Using an input mask will not only ensure that the data is in a particular format, but it also saves you the trouble of typing certain characters, like parenthesis or hyphens. In the telephone number example, Access would not let you enter a number without an area code nor would it let you save a record that did not contain the required number of characters. For common types of entries such as phone numbers, social security numbers, dates, or zip codes, use the Input Mask Wizard by clicking on the Build button in the Input Mask property box and then choose one of the available pre-defined Input Masks. The Input Mask Wizard For entries not supported by the Input Mask Wizard, you can create your own Input Mask manually. The table below lists the characters available to create a manual Input Mask. The Input Mask contains 3 sections, each separate by a semicolon: 1. The mask characters listed in the table below. 2. Enter 1 if you want all literal placeholders to be saved with the data. Enter 0 (or leave blank) to save only the characters in the field. © 2016 PCM Courseware, LLC 63 L E S S O N 2 – W O R K I N G W I T H T A B L E S 3. Optional: Enter the placeholder that you want to appear on-screen as the user enters data. For example, let’s say we had an employee number in the following format: ########. The first 6 characters are required and must be numbers, and the two characters after the dash can be letters or numbers and are optional. We also want an underscore (_) as a placeholder. Thus, our manual Input Mask would be entered as 000000-aa;1;_. See the breakdown below. Part 2: 0 to save literals, 1 to save only characters entered Part 1: Input Mask Characters Part 3: Placeholder displayed 000000-aa;1;_ as user enters data Input Mask Characters Character Description 0 Number (0 through 9, entry required; plus and minus signs are not allowed). 9 Number or space (entry optional; plus and minus signs not allowed). # Number or space (entry optional; plus and minus signs allowed). L Letter (A through Z, entry required). ? Letter (A through Z, entry optional). A Letter or number (entry required). a Letter or number (entry optional). & Any character or a space (entry required). C Any character or a space (entry optional). .,:;-/ Decimal placeholder and thousands, date, and time separators. < Converts all characters that follow to lowercase. > Converts all characters that follow to uppercase. ! Characters are displayed from right to left, rather than from left to right. \ Causes the character that follows to be displayed as a literal character (for example, \A is displayed as just A). Access 2016 Level 2 64 L E S S O N 2 – W O R K I N G W I T H T A B L E S Character Password Description Creates a password entry text box. Any character typed in the text box is stored as the character but is displayed as an asterisk (*). To Create an Input Mask 1. Select the table that contains the field for which you want to create an Input Mask. Switch to Design View. Click anywhere in the row of the field to receive the Input Mask. To enter an Input Mask manually, type the desired characters in the Input Mask box in the Field Properties area. To enter an Input Mask using the wizard: a. Click in the Input Mask box in the Field Properties area. b. Click the Build button. c. Chose the desired Input Mask from the list box. d. Click Next. e. Make any desired changes to the Input Mask characters or to the placeholder. f. Click Next. g. Select whether to store data with the literals or without the literals. h. Click Finish. 2. 3. 4. 5. Let’s Try It! What Why 1. Click in the Field Name column for the HomePhone field. Displays the field properties for the HomePhone field. 2. Under Field Properties, click in the Input Mask box. Activates the Input Mask field property for the HomePhone field. 3. Click the Build button on the right edge of the Input Mask box as shown below. Click Yes if asked to save the table. Launches the Input Mask Wizard. © 2016 PCM Courseware, LLC 65 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 4. Select Phone Number from the Input Mask window as shown. Selects the Input Mask to use. 5. Click Next. Moves to the next step of the Wizard. 6. Click the Placeholder Character arrow and then select # from the drop-down list as shown below. Selects the placeholder we want use in our Input Mask. Access 2016 Level 2 66 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 7. Click Next. Moves to the next step of the Wizard. 8. Click Finish. As we do not wish to save the literal characters with the data, we will accept the default value. 9. Click the Save icon on the Quick Access Toolbar. Saves our design changes. 10. Click the View button. Switches to Datasheet View. 11. Click in the Zip field for Jaime Rickman Sets the focus in the Zip field. 12. Press Tab. Moves the focus to the HomePhone field. 13. Type: 4045550922 Enters the phone number without having to enter a parenthesis around the area code or any dashes. 14. Press the Ctrl + W keystroke combination.. Closes tblCusotmers. © 2016 PCM Courseware, LLC 67 L E S S O N 2 – W O R K I N G W I T H T A B L E S 2.6 Creating a Lookup Field In this lesson, you will learn how to create a Lookup Field. L ookup Fields allow you choose the data for a field from a list of values, usually from a query or from another table. Let’s say you were entering customer orders. If you remember from the last lesson, only the Customer ID is entered in the Orders table, not the customer’s name. So how do you know what Customer ID goes with a particular customer? That’s where a lookup field comes in. Using a lookup field, you can get a list of all customer names from the Customers table, and then choose which customer to enter into the orders table. Even though the customer names are displayed in the list, you set up your lookup field so that the Customer ID is stored in the field (in the case of our Orders table, only the Customer ID can be stored). The best thing about creating a Lookup Field is that the Lookup Wizard will step you through the process of creating it. The Lookup Wizard is the last option in the Data Type drop-down list. If you have already created relationships, you may have to delete them in order to change the Data Type to the Lookup Wizard. Thus, it is advisable to create the Lookup fields in your table before establishing your relationships. Access 2016 Level 2 68 L E S S O N 2 – W O R K I N G W I T H T A B L E S To Create a Lookup List 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. Select the table that contains the field to which you want to add a Lookup field. Switch to Design View. Click the Data Type arrow for the field to receive the Lookup list and then select Lookup Wizard. Select the option that indicates you want the Lookup field to look up the values in a table or query. Click Next. Choose the table or query from where the lookup data will be retrieved. Choose which field(s) are to be displayed in the Lookup List. Adjust the columns to the desired width. Notice that the Hide Key Column is selected. Uncheck this box to display the Primary Key column (which will be the bound column). Click Next. Type the desired label for the Lookup column. Click Finish. Let’s Try It! What Why 1. Double-click tblOrders and then click the View button. Displays tblOrders in Design View. 2. Click in the Data Type column for the CustomerID field. Displays the Data Type arrow. 3. Click the arrow and then select Lookup Wizard from the dropdown list as shown below. Launches the Lookup Wizard. © 2016 PCM Courseware, LLC 69 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 4. Select the option that indicates you want the Lookup field to look up the values in a table or query. Sets the option for the Lookup field to look up the values in an existing table or query. 5. Click Next. Moves to the next step of the Wizard. 6. Select tblCustomers as shown below and then click Next. Selects tblCustomers as the Lookup table (the table from where we will receive our list values) and then moves to the next step of the Wizard. Access 2016 Level 2 70 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 7. Double-click Last Name in the Available Fields window as shown below. Adds the Last Name to the Selected Fields window. These fields will make up the lookup list. We do not need to select the CustomerID field as it is selected automatically but is hidden from view. 8. Double-click First Name in the Available Fields window. Adds the First Name field to the Selected Fields window. 9. Click Next. Moves to the next step of the Wizard. 10. Click the arrow to the right of the first combo box and select LastName as shown below. © 2016 PCM Courseware, LLC You can specify ascending or descending sort order of up to four fields in the Lookup Wizard. This feature is also available in the List Box Wizard and the Combo Box Wizard. 71 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 11. Click Next. Moves to the next step of the Wizard. 12. Click Next. We will keep the default column width and then move to the next step of the Wizard. 13. Click Finish. Completes the Lookup Wizard. 14. When the message displays informing you that the table must be saved before the relationship can be created, click Yes. Saves the design changes of the table. 15. Click the View button. Switches to Datasheet View. Notice that the CustomerID for the first record is displayed as Nolan. The actual value in this field is the CustomerID data – it is just hidden from view. 16. Click in the CustomerID field. of the first record. Displays the arrow for the CustomerID field in the first record. Access 2016 Level 2 72 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 17. Click the arrow and then choose Cathy Powell from the dropdown list. Changes the CustomerID to Cathy Powell for this record. © 2016 PCM Courseware, LLC 73 L E S S O N 2 – W O R K I N G W I T H T A B L E S 2.7 Creating a Value List In this lesson, you will learn how to create a Value List. A Value List is similar to a Lookup field except that instead of looking up the values in another table, you type in the values you want to be displayed. If there are only a few items that need to be displayed in a drop-down list, using a Value List is preferable to creating an entire table to hold just a few values to serve as a Lookup List. Use the Lookup Wizard to create a Value List just like we did in the last section. However, choose “I will type in the values that I want’ in the first step of the Wizard, and then manually enter in the items that are to appear in your list. To Create a Value List 1. 2. 3. 4. 5. 6. 7. Select the table that contains the field to which you want to add a Value List. Switch to Design View. Click the Data Type arrow for the field to receive the Value list and then select Lookup Wizard. Select the option “I will type in the values that I want” Click Next. Choose the number of columns that are to be displayed in the list. Enter the data in the columns that should appear in your list. Access 2016 Level 2 74 L E S S O N 8. 9. 10. 2 – W O R K I N G W I T H T A B L E S Click Next. Type the desired label for the Value List. Click Finish. Let’s Try It! What Why 1. Click the View button on the Ribbon. Displays tblOrders in Design View. 2. Click in the Data Type column for the PaymentMethod field. Displays the Data Type arrow. 3. Click the arrow and then select Lookup Wizard from the dropdown list. Launches the Lookup Wizard. 4. Select the option “I will type in the values that I want” as shown below. Sets the option for the Lookup field to get its values from a manually typed list. 5. Click Next. Moves to the next step of the Wizard. 6. Click in the first blank column and type: Visa as shown below. Enters the first value of our Value List. © 2016 PCM Courseware, LLC 75 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 7. Press the Tab key. Moves to a new row. 8. Type: MasterCard Enters the next value of our Value List. 9. Press the Tab key. Moves to a new row. 10. Type: American Express Enters the next value of our Value List 11. Click Next. Moves to the next step of the Wizard. 12. Click Finish. Completes the Lookup Wizard. 13. Click the Save icon on the Quick Access Toolbar. Saves our design changes. 14. Click the View button on the Ribbon. Switches to Datasheet View. Access 2016 Level 2 76 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 15. Click in the PaymentMethod field for the first record. Displays the arrow for the PaymentMethod field. 16. Click the arrow and then select MasterCard from the dropdown list as shown below. Selects “MasterCard” from the Value List. This value will be stored in the PaymentMethod field. © 2016 PCM Courseware, LLC 77 L E S S O N 2 – W O R K I N G W I T H T A B L E S 2.8 Modifying a Value List In this lesson, you will learn how to modify a Value List. M odifying a Value List is almost as easy as creating one. To modify a value list, click the Lookup tab in the field properties for the field whose Value List you wish to modify and then enter the changes in the Row Source box. Each entry must be surrounded by quotes and separated from the other entries by a semicolon. To Modify a Value List 1. 2. 3. 4. 5. 6. Select the table whose value list you wish to modify. Click the Design View button. Click anywhere in the row of the field whose value list you wish to modify. Click the Lookup tab in the Field Properties area. Click in the Row Source box in the Field Properties area. Type your changes. Access 2016 Level 2 78 L E S S O N 2 – W O R K I N G W I T H T A B L E S Let’s Try It! What Why 1. Click the View button on the Ribbon. Opens tblOrders in Design View. 2. Click in the Data Type column for the PaymentMethod field. Displays the Field Properties for the PaymentMethod field. 3. Click the Lookup tab under Field Properties. Displays Value List/Lookup List properties. 4. Click in the Row Source box after the words “American Express” Sets the insertion point in the Row Source box where we are going to add a new Value List entry. 5. Type: ; Enters a semicolon. A semicolon is used to separate one value list from another. 6. Type: “Discover” as shown below. Make sure that you surround the entry with quotation marks. Enters a new Value List item. 7. Click the View button on the Ribbon. Click Yes when asked to save changes. Saves our design changes and then switches to Datasheet View. 8. Click in the PaymentMethod field for the first record. Displays the arrow for the PaymentMethod field. 9. Click the arrow and then select Discover from the drop-down list. Selects our new Value List item, “Discover”, from the Value List. © 2016 PCM Courseware, LLC 79 L E S S O N 2 – W O R K I N G W I T H T A B L E S 2.9 Creating Calculated Fields In this lesson, you will learn how to create a calculated field in a table. A ccess allows you to create calculated fields in tables. In older (pre-2010) versions of Access, you could only create calculated expressions on the fly with queries and reports. With the Calculated data type, you can now create calculations directly in your tables. To Create a Calculated Field in a Table 1. 2. 3. 4. 5. 6. 7. 8. Select the table into which you want to create a calculated field. Click the Design View button. Click in the first blank row in the Field Name column and type in a name for your new field. Click in the Data Type column for the field. Click the drop-down arrow in the Data Type column and choose Calculated from the list to display the Expression Builder dialog box. In the Expression Categories column, double-click the name of the first field you want to add to the calculation. Enter any operators such as +, -, / or *. In the Expression Categories column, double-click the name of the second field you want to add to the calculation. Access 2016 Level 2 80 L E S S O N 9. 10. 2 – W O R K I N G W I T H T A B L E S Repeat steps 7-8 for any additional fields you want to add to the calculation. When finished, click OK. Tip: To create a calculated field in Datasheet View, click in the first blank column, and click the contextual Fields tab on the Ribbon. Then, click the More Fields button on the Ribbon, point to Calculated Field, choose the data type for the fields from the menu and then create your expression. Let’s Try It! What Why 1. Click the Close button on tblOrders. Closes tblOrders. 2. Right-click tblYearlySubscriptions and then click Design View from the menu. Displays tblYearlySubscriptions in Design View. 3. Click in the first blank row in the Field Name column and type: Due as shown below. Names the new field Due. 4. Press Tab. Moves to the Data Type row. 5. Click the Data Type drop-down arrow and choose Calculated from the list. Displays the Expression Builder dialog box 6. Double-click the AmountDue field in the Expression Categories window as shown below. Adds the AmountDue field to the expression window. © 2016 PCM Courseware, LLC 81 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 7. Type the minus (-) symbol on your keyboard. Adds a minus symbol to the Expression window. 8. Double-click the AmountReceived field in the Expression Categories window. Click OK. Adds the AmountDue field to the expression window and then closes the window. 9. In the Field Properties area, click in the Format property box. Displays the drop-down arrow for the Format property. 10. Click the drop-down arrow and choose Currency from the list. Sets the format of the Due field to currency. 11. Click the View button on the Ribbon. Click Yes when asked to save the table. Saves our table changes and switches to Design View. Notice that the Due field automatically calculates the difference between the AmountDue and AmountReceived fields. Access 2016 Level 2 82 L E S S O N 2 – W O R K I N G W I T H T A B L E S 2.10 Creating Multiple Primary Keys In this lesson, you will learn how to create multiple primary keys in a table. Y ou have already learned that a primary key field prevents the entry of duplicate data in a field. For instance, if the customer number is set as a primary key field, you can only enter each customer number once in that field. However, there are instances where you wish to add more than one primary key field. Multiple primary keys prevent the entry of duplicate records of all primary key fields together. Suppose we have a Student Classes table and we set the Student ID field and the Class ID field as primary key fields. In this case, you could enter duplicate Student ID records and duplicate Class ID records, but not a duplicate of the same Student ID and Class ID together. Look at the table below for an example. Smith and Jones can each take more than one class and each class can be taken by more than one student, but neither Smith nor Jones can take the same class more than once. Student ID Class ID Smith French Smith History Jones English Jones French To Set Multiple Primary Keys 1. 2. Select the table for which you want to set multiple primary keys. Switch to Design View. © 2016 PCM Courseware, LLC 83 L E S S O N 3. 2 – W O R K I N G W I T H T A B L E S Select the rows that you want to set as primary key fields (to select nonadjacent rows, hold down the Ctrl key and then select the rows). Click the Primary Key button on the Tools group on the Ribbon. 4. Let’s Try It! What Why 1. Click the Close button on tblYearlySubscriptions. Closes tblYearlySubscriptions. 2. Click the File tab on the Ribbon and click Close. Closes the Rodneys Video 2 database and brings you to a blank Access window. 3. Click the File tab on the Ribbon and click Open. Displays the Open pane. 4. Click Browse in the Center pane. Displays the Open window. 5. Click Desktop in the left pane and then double-click Lesson Files in the right pane. Displays the contents of the Lesson Files folder. 6. Click the Class Registration 2 file and then click Open. Opens the Class Registration 2 database. 7. Right-click the StudentClasses table and then click Design View. Displays StudentClasses in Design View. 8. Move your mouse pointer over the record selector for the StudentID field until the cursor transforms into a right-pointing arrow and then click and drag downwards until all fields are selected as shown below. Selects all fields. To ensure that no duplicate records are entered, we will set multiple primary keys for the StudentID, ClassID, Semester and Year fields. Access 2016 Level 2 84 L E S S O N 2 – W O R K I N G W I T H T A B L E S What Why 9. Sets all selected fields as primary key fields. Click the Primary Key icon on the Ribbon. 10. Click the View button on the Ribbon. Click Yes when asked if you want to save your changes. Saves the design changes and then switches to Datasheet View. Observe the records. 11. Double-click in the ClassID field for the last record. Selects the ClassID field for student 45. 12. Type: 34 Changes the Class ID from 35 to 34. 13. Press the Up Arrow key. You receive an error message because now you would have a duplicate record for all four fields. 14. Click OK. Closes the error message box. 15. Press the Esc key. Restores the original value. 16. Click the Close button on the StudentClasses table. Closes the StudentClasses table. © 2016 PCM Courseware, LLC 85 L E S S O N 2 – W O R K I N G W I T H T A B L E S 2.11 Creating Multiple Field Values In this lesson, you will learn how to create multiple field values in a table. Y ou have already learned that you can choose an item from a Lookup Field in a table. Access also provides the ability to store multiple values in a field using the Lookup Wizard. For instance, you may want to assign an employee to more than one supervisor or to more than one task. When you click the drop-down arrow on a multi-value field, the value list displays with a check box next to each item, allowing the user to check the value or values they want to store in the field. To create a multiple field value, click the Allow Multiple Values check box on the last screen of the Lookup Wizard. To Create a Lookup List 1. 2. 3. 4. 5. 6. 7. Select the table that contains the field to which you want to add a Lookup field. Switch to Design View. Click the Data Type arrow for the field to receive the Lookup list and then select Lookup Wizard. Click the “I want the Lookup field to look up the values in a table or query” radio button. For a value list, choose “I will type in the values that I want”. Select your lookup options – either enter in the values you want or choose the table or query that contain the values you want to use. On the last screen of the wizard, click the Allow Multiple Values check box. Click Finish. Access 2016 Level 2 86 L E S S O N 2 – W O R K I N G W I T H T A B L E S Let’s Try It! What Why 1. Double-click the Students table and then click the View button. Displays the Students table in Design View. 2. Click in the Data Type column for the Advisor field. Displays the Data Type arrow. 3. Click the arrow and then select Lookup Wizard from the dropdown list. Launches the Lookup Wizard. 4. Select the option that indicates you want the Lookup field to look up the values in a table or query. Sets the option for the Lookup field to look up the values in an existing table or query. 5. Click N...
Purchase answer to see full attachment