ACCESS: defines the relationship between tables.

In the Access database, there is a relationship between data in different tables. This relationship Associates each data record in each table in the database with a unique topic in the database, this makes all operations on a data database an overall operation. Take the tables created in the previous lessons as an example.

In the Access database, there is a relationship between data in different tables. This relationship Associates each data record in each table in the database with a unique topic in the database, this makes all operations on a data database an overall operation. Take the tables created in the previous lessons as an example.

"Relationship" Introduction

In the Access database, there is a relationship between data in different tables. This relationship Associates each data record in each table in the database with a unique topic in the database, this makes all operations on a data database an overall operation ".

Take the tables created in the previous lessons for example. Many of the values in the "Company Name" in the "Customer Info table" and "order unit" in the "Order info table" are the same. Because the "Order unit" that has signed the order must already be the customer of the company, the names of these customers are also recorded in the "Company Name" Field of the "customer information table. When we know the name of a customer, we can know its "customer information" through the "customer information table ", you can also use the order information table to learn the order information it has signed ", therefore, the "Company Name" serves as a link to connect the corresponding field information in the "customer data table" and "order information table". In order to reflect this data relationship between tables in the database, access provides a method to establish a "relationship" between a table and a table. Data that establishes a link in this way can be called and used only by a topic, which is very convenient.

After establishing such a relationship between the "customer information table" and "order information table", we only need to check the "customer information table". without adding the table content, you can see the information of all customers and all their "orders", so that the data is clear at a glance.

Three relationships between tables

Before establishing the relationship between tables, we still need to mention the three types of "relationship": "One-to-one", "one-to-many", and "many-to-many ". In Access, the "one-to-one" and "one-to-many" relationships can be directly established between two tables, while the "many-to-many" relationship must be implemented through the "one-to-many" relationship. Let's talk about these three relationships respectively ".

The first is the "one-to-one" relationship, for example, the following two tables: monthly supplementary food allowance for a school student:

And a school student monthly grant:

We reduced the data in these two tables to generate a new table: "Summary of the monthly real-time amount of a school student ":

Because the name field may be duplicated, the two tables can be uniquely linked only by the "student ID" field that cannot contain duplicate values, make sure that the "Supplemental Food Allowance" and "Scholarship" will not be incorrectly issued, so that one student ID in the "Monthly supplementary food allowance" will only correspond to one student ID in the "monthly allowance" and will never be mistaken. In this way, the relationship between the two tables is obviously "one-to-one.

Next let's take a look at the "one-to-many" relationship. In the "Customer Info table" and "Order info table", the "Order unit" field in the "Order info table" is the same as the "Company Name" field in the "Customer Info table. In fact, only the units ordered by the company are the customers of the company. Therefore, the two tables are linked by the name of the customer company. However, each customer can order multiple orders. Therefore, a company name in the customer information table corresponds to several orders in the order information table ". Such a relationship is "one-to-many ".

Finally, we know that there are many books in the bookstore, and a book can also be found in many bookstores. At this time, the relationship between "books" and "Bookstore" is "many-to-many.

How to establish a relationship between tables

Now, create a "relationship" between tables. First, click the "relationship" command under the "Tools" menu to bring up the "relationship" dialog box. A "display table" dialog box is displayed, as shown in, in the "show tables" dialog box, you can add "tables" or "queries" to the "relationship" dialog box.

Select the "Customer Info table" and "Order info table" tables and click "add" to add them to the "Link" dialog box, click "close" to close the "display table" dialog box. When you need to open it later, you just need to right-click the "Link" dialog box and select the "display table" command.

In the "relationship" dialog box, only the "Customer Info table" and "Order info table" fields are listed. How can we establish a relationship? In fact, tables are composed of fields, and the relationship between tables is also associated by fields. After two fields in different tables are connected, other fields in the table can be linked by the relationship between the two fields. That is to say, you can establish a relationship between the "Company Name" and "order unit" fields in the "customer information table. In the "Customer Info table" Field List, select the "Company Name" item, press and hold the left mouse button, and drag the mouse to the "Order unit" item in the "Order info table", and release the left mouse button, the Edit relationship dialog box is displayed.

This dialog box can help us edit the established relationship. The source of the two fields of the link can be changed through the list box on the left. You can click "new..." to create a new link, or click "Join type" to select a join type for the link. Click the join type button. In the displayed dialog box, select the third item and click OK. Return to the "Edit link" dialog box and click "CREATE.