Microsoft Office 2013最新教程 (12).ppt

1、Microsoft Office Access 2007 Training,Build a database III: Build relationships for a new Access database,Your company name presents:,Build a database III: Build relationships for a new Access database,Course contents,Overview: You can relate Lesson: Create relationships,The lesson includes a list o

2、f suggested tasks and a set of test questions.,保险超市 ,Build a database III: Build relationships for a new Access database,Overview: You can relate,This course teaches you how to build relationships between your new tables. Why are relationships essential? They bring the data in your tables together s

3、o you can extract meaningful information. This course explains the different types of relationships you can use in a database, and shows you how to create the most common type: a one-to-many relationship.,Build a database III: Build relationships for a new Access database,Course goals,Create one-to-

4、many relationships between database tables. Set referential integrity for each relationship and prevent accidental data removal. Set cascading updates and deletes, a move that walks any changes through your data. Turn indexing on or off for primary and foreign key fields, a move that helps speed you

5、r database and create a type of relationship called a one-to-one relationship.,Lesson,Create relationships,Build a database III: Build relationships for a new Access database,Create relationships,In a database, relationships are how your tables “talk” to each other. If your tables dont talk, you can

6、t get answers from your data.,You create relationships by including a field from one table as a field in a related table.,Build a database III: Build relationships for a new Access database,Create relationships,In a database, relationships are how your tables “talk” to each other. If your tables don

7、t talk, you cant get answers from your data.,The field you include is called a primary key. Each of the tables in your database has a primary key, and you include it as a new field in another table to create a relationship with that other table.,When you share a primary key, the new field in the sec

8、ond table is called a foreign key.,Build a database III: Build relationships for a new Access database,Create relationships,In a database, relationships are how your tables “talk” to each other. If your tables dont talk, you cant get answers from your data.,The picture illustrates one way this can w

9、ork. In order to associate assets with suppliers, the foreign key field in the Assets table provides a list of company names. Behind the scenes, Access uses the values in the primary and foreign keys to synchronize the two fields.,Build a database III: Build relationships for a new Access database,T

10、ypes of relationships,Your tables can participate in three types of relationships, shown in the picture.,You have a one-to-one relationship when a single record in one table is related to a single record in another table, and vice versa. You have a one-to-many relationship when one record in a table

11、 is related to many records in another table.,Build a database III: Build relationships for a new Access database,Types of relationships,Your tables can participate in three types of relationships, shown in the picture.,You have a many-to-many relationship when several records in one table are relat

12、ed to several records in another table.,Build a database III: Build relationships for a new Access database,Create a relationship,So lets create a one-to-many relationship between the Suppliers and Assets tables.,Before you start, think of the relationship as having “sides.” In this case, one suppli

13、er can provide many assets, so the Suppliers table resides on the “one” side of the relationship, while the Assets table sits on the “many” side.,Build a database III: Build relationships for a new Access database,Create a relationship,Next, remember this rule: To create a one-to-many relationship,

14、you use the primary key from the “one” side as a foreign key on the “many” side.,One of the easiest ways to do this is to run a tool called the Lookup Wizard, which creates the relationship and the foreign key field.,Animation: Right-click, and click Play.,Build a database III: Build relationships f

15、or a new Access database,Create a relationship,Next, remember this rule: To create a one-to-many relationship, you use the primary key from the “one” side as a foreign key on the “many” side.,One of the easiest ways to do this is to run a tool called the Lookup Wizard, which creates the relationship

16、 and the foreign key field. Whats more, the foreign key works as a drop-down list, so you can select data from the table on the “one” side quickly and accurately.,Build a database III: Build relationships for a new Access database,Set referential integrity,The relationship you created in the previou

17、s section doesnt become a one-to-many relationship until you set referential integrity.,Referential integrity ensures that your primary and foreign key fields stay in synch whenever you add, change, or remove data. In turn, that keeps your data accurate.,Build a database III: Build relationships for

18、 a new Access database,Set referential integrity,You set referential integrity by editing a relationship.,Close any open tables, click the Database Tools tab, and in the Show/Hide group, click Relationships. Click the line that represents the relationship you want to edit. The line becomes thicker.,

19、Build a database III: Build relationships for a new Access database,Set referential integrity,You set referential integrity by editing a relationship.,Right-click the line and click Edit Relationship. The Edit Relationship dialog box appears.,Select the Enforce Referential Integrity check box.,Build

20、 a database III: Build relationships for a new Access database,Set referential integrity,You set referential integrity by editing a relationship.,Optionally, you can change data in one place and have that change appear in other related data. To do so, select the Cascade Update Related Fields and Cas

21、cade Delete Related Records check boxes. When you finish, click OK.,Build a database III: Build relationships for a new Access database,A look at one-to-one relationships,You may not use one-to-one relationships very often, but you should know how to create them.,One-to-one relationships use a prima

22、ry key and a foreign key, just like one-to-many relationships. The difference lies in how you set indexes for each key field. Indexing a field allows Access to search for data faster. It can also prevent users from adding duplicate records.,Build a database III: Build relationships for a new Access

23、database,A look at one-to-one relationships,You use Design View to turn on indexing for your foreign key fields. Its easiest to do this with blank tables.,In the Navigation Pane, right-click the table that contains the foreign key field, then click Design View. In the designer, click the foreign key

24、 field. Under Field Properties, on the General tab, click the list next to Indexed and select Yes (No Duplicates).,The picture shows the process.,Build a database III: Build relationships for a new Access database,A look at many-to-many relationships,You have a many-to-many relationship when a singl

25、e record in one table can relate to many records in another, and a single record in that second table can also relate to many in the first.,For example, say your company has several types of computers and several technicians, with each technician certified to work on some, but not all, of the comput

26、ers. Each technician can be related to more than one computer, and in turn, each computer can be related to more than one technician.,Build a database III: Build relationships for a new Access database,A look at many-to-many relationships,To track who can work on a given machine, you create a many-t

27、o-many relationship by adding the primary keys from both sides of the relationship to a third table, called a junction or link table.,The picture shows a typical junction table, and you can see that its structure is relatively simple a pair of primary key fields and some related information. Now you

28、r data can answer questions such as, “Which computers did a given technician work on last month?”,Build a database III: Build relationships for a new Access database,Suggestions for practice,Create a relationship. Set referential integrity and cascading changes.,Online practice (requires Access 2007

29、),Build a database III: Build relationships for a new Access database,Test question 1,Its a good idea to think of a relationship as having “sides.” (Pick one answer.),True. False.,Build a database III: Build relationships for a new Access database,Test question 1: Answer,True.,Knowing which side a t

30、able is on tells you whether or not it uses a foreign key field.,Build a database III: Build relationships for a new Access database,Test question 2,To create a one-to-many relationship, you do which of the following? (Pick one answer.),Use the primary key from the table on the “one” side as a foreign key in the table on the “many” side. Use a junction table with at least two foreign key fields. Turn off indexing for the primary key field.,Build a database III: Build relationships for a new Access database,Test ques