Creating Relationships Between Tables in Access 2010

2. Access 2010 Level 1 Unit 1 Creating Tables and Queries Chapter 2Creating Relationships between Tables

3. Creating Relationships between Tables Quick Links to Presentation Contents • Create Related Tables • CHECKPOINT 1 • Create a One-to-One Relationship • Display Related Records in a Subdatasheet • CHECKPOINT 2

4. Create Related Tables • Generally, a database management system fits into one of two categories—either a file management system (also sometimes referred to as a flat file database) or a relational database management system.

5. Create Related Tables…continued • Taking time to plan a database is extremely important. • Creating a database with related tables takes even more consideration. • You need to determine how to break down the required data and what tables to create to eliminate redundancies.

6. Create Related Tables…continued • Along with deciding on the necessary tables for a database, you also need to determine the relationship between tables. • The ability to relate, or “join,” tables is what makes Access a relational database system. database diagram

7. Create Related Tables…continued • When a new record is added to a table, Access checks to ensure that there is no existing record with the same data in the primary key. If there is, Access displays an error message indicating there are duplicate values and will not allow the new record to be saved. • When adding a new record to a table, the primary key field cannot be left blank. Access expects a value in each record in the table and this is referred to as entity integrity.

8. Create Related Tables…continued To display a table in Design view: • Open the desired table. • Click the View button in the Views group in the Home tab. View button

9. Create Related Tables…continued To add or remove a primary key from a field: • Click the desired field in the Field Name column. • Click the Table Tools Design tab. • Click the Primary Key button in the Tools group. Primary Key button

10. Create Related Tables…continued • Typically, a primary key field in one table becomes the foreign key field in a related table. primary key and associated foreign key

11. Create Related Tables…continued • In Access, one table can be related to another, which is generally referred to as performing a join. For example, in Project 1b, you will create a relationship between the Suppliers table and the Products table. The relationship you establish will ensure that a supplier number cannot be entered in the Products table without first being entered in the Suppliers table. • This type of relationship is called a one-to-many relationship, which means that one record in the Suppliers table will match zero, one, or many records in the Products table.

12. Create Related Tables…continued To create a one-to-many relationship: • Open the desired database. • Click the Database Tools tab. • Click the Relationships button in the Relationships group. continues on next slide… Relationships button

13. Create Related Tables…continued • At the Show Table dialog box, click the first table name. • Click the Add button. • Repeat Steps 4 and 5 until all desired table names have been added. • Click the Close button. continues on next slide… Show Table dialog box

14. Create Related Tables…continued • At the Relationships window, drag the common field to the related table. continues on next slide… Relationships window

15. Create Related Tables…continued • At the Edit Relationships dialog box, check to make sure the correct field name displays and the relationship type displays as One-To-Many. • Specify the relationship options. • Click the Create button. Edit Relationships dialog box

16. Create Related Tables…continued • Choose Enforce Referential Integrity at the Edit Relationships dialog box to ensure that the relationships between records in related tables are valid. • Referential integrity can be set if the field from the primary table is a primary key and the related fields have the same data type.

17. Create Related Tables…continued • In the image below, the Suppliers table displays with a black line attached along with the number 1 (signifying the “one” side of the relationship). The black line is connected to the Products table along with the infinity symbol (signifying the “many” side of the relationship). join line

18. Create Related Tables…continued To print relationships: • Click the Database Tools tab. • Click the Relationships button. • Click the Relationship Report button in the Tools group. continues on next slide… Relationship Report button

19. Create Related Tables…continued • Click the Print button. • Click OK. • Click the Close button. Print button

20. Create Related Tables…continued To display the Show Table dialog box: • Click the Show Table button in the Relationships group. Show Table button

21. Create Related Tables…continued To edit a relationship: • Click the Database Tools tab. • Click the Relationships button in the Relationships group. • Click the Edit Relationships button in the Tools group. • Make the desired changes at the Edit Relationships dialog box. • Click OK. Edit Relationships dialog box

22. Create Related Tables…continued To delete a relationship: • Click the Database Tools tab. • Click the Relationships button in the Relationships group. • Right-click on the black line connecting the related tables. • Click the Delete option. • Click Yes. Delete option

23. CHECKPOINT 1 • Access is this type of database management system. • flat file • relational • directional • organizational • The infinity symbol signifies this side of the relationship. • many • one • two • three Answer Answer Next Question Next Question • Typically, a primary key field in one table becomes this key field in a related table. • figure • international • foreign • secondary • To edit a relationship, click the Relationships button in this tab. • Home • Database Design • Database Tools • Create Answer Answer Next Question Next Slide

24. Create a One-to-One Relationship • You can create a one-to-one relationship between tables in which each record in the first table matches only one record in the second table and one record in the second table matches only one record in the first table. one-to-one relationship

25. Create a One-to-One Relationship…continued To create a one-to-one relationship: • Open the desired database. • Click the Database Tools tab. • Click the Relationships button in the Relationships group. continues on next slide… Relationships button

26. Create a One-to-One Relationship…continued • At the Show Table dialog box, click the first table name. • Click the Add button. • Repeat Steps 4 and 5 until all desired table names have been added. • Click the Close button. continues on next slide… Show Table dialog box

27. Create a One-to-One Relationship…continued • At the Relationships window, drag the common field to the related table. continues on next slide… Relationships window

28. Create a One-to-One Relationship…continued • At the Edit Relationships dialog box, check to make sure the correct field name displays and the relationship type displays as One-To-One. • Specify the relationship options. • Click the Create button. Edit Relationships dialog box

29. Display Related Records in a Subdatasheet • When a relationship is established between tables, you can view and edit records in related tables with a subdatasheet. subdatasheet

30. Display Related Records in a Subdatasheet…continued To display a subdatasheet: • Click the expand indicator at the left side of the desired record. • Click the desired table at the Insert Subdatasheet dialog box. Insert Subdatasheet dialog box

31. Display Related Records in a Subdatasheet…continued To display subdatasheets for all records: • Click the More button in the Records group in the Home tab. • Point to Subdatasheet. • Click the Expand All option. Expand All option

32. Display Related Records in a Subdatasheet…continued To remove a subdatasheet: • Click the More button in the Records group in the Home tab. • Point to Subdatasheet. • Click the Remove option. Remove option

33. CHECKPOINT 2 • This type of relationship is used when you want to break a large table into two smaller tables. • many-to-many • many-to-one • one-to-many • one-to-one • To display a subdatasheet, click this preceding the record. • view indicator • expand indicator • collapse indicator • remove indicator Answer Answer Next Question Next Question • Add tables to the Relationships window at this dialog box. • Show Table • Show Relationships • Edit Relationships • Edit Table • To display all subdatasheets, start by clicking this button in the Home tab. • View • Expand • Display • More Answer Answer Next Question Next Slide

34. Creating Relationships between Tables Summary of Presentation Concepts • Define a primary key in a table • Create a one-to-many relationship • Specify referential integrity • Print, edit, and delete relationships • Create a one-to-one relationship • View and edit a subdatasheet