ACCESS: Defining relationships between tables

Introduction to "Relationships"

In an Access database, there is a relationship between the data in different tables, which links each data record in each table in the database to the only subject in the database, making the operation of a single data the whole operation of the database, the so-called "far-reaching".

Take a few lessons to set up the table, the "Customer Information table" in the "Company name" and "Order Information table" in the "Order Unit" contains a number of values are the same. The name of the customer is also recorded in the company Name field in the Customer information table because the "order unit" that signed the order must be a customer of the company. When we know the name of a customer, we can understand its "customer information" through the "Customer Information table", "Order Information" can also be understood through the "Order Information table", so "company name" as a link to the corresponding field information in the "Customer table" and "Order Information table", In order to manifest this data relationship between tables in a database, Access provides a way to establish a "relationship" between tables and tables. It is convenient to use this method to set up the relationship data, which can be used only by a theme.

When we build this relationship in the Customer information table and the Order Information table, we just need to look at the Customer information table and see all of the customer's information and all of its "orders" without adding the contents of the table, to make these data clear at a glance.

Three relationships between tables

Before creating a relationship between tables, we still need to mention the three types of "relationships", "one-to-one", "One-to-many" and "Many-to-many." In Access, you can create a "one-to-one" and "one-to-many" relationship directly between two tables, whereas a many-to-many relationship is implemented through a one-to-many relationship. We say these three kinds of "relationships" separately.

First is the "one-to-one" relationship, such as the following two tables: a school student monthly non-staple food subsidy:

and a school student monthly grant:

We drop the data in these two tables to make a summary of the new table: "A school student monthly real Fat sum":

Because the last Name field may be duplicated, it is only possible to "learn numbers" through fields that do not have duplicate values. The two tables can only be linked to ensure that "non-staple food subsidy" and "bursaries" will not be wrongly issued, so that "monthly non-staple food supplement form" in a school number only corresponds to the "monthly grant" in a school number , absolutely not mistaken. The two-table relationship is clearly "one-to-one".

Here's a look at "one-to-many" relationships. In the Customer Information table and order Information table, the values in the Order Unit field and the Customer information table in the Company Name field in the Order Information table are the same. In fact, only those units that ordered the company's customers, so the two tables are linked to the name of the customer company. But each customer can subscribe to many orders. Therefore, a "company name" in the "Customer Information table" can correspond to several "orders" in the Order Information table. Such a relationship is "One-to-many".

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

How to establish a relationship between tables

Now we start to create relationships between tables, first by clicking Relationships under the Tools menu, the Relationship dialog box pops up with a show Table dialog box with the following figure, and the Show Table dialog box allows you to add a table or query that requires a relationship to the Relationships dialog box.

Select the two table "Customer Information table" and "Order Information table", click the Add button to add them all to the Relationships dialog box, and click the Close button to close the Show Table dialog box. When you need to open it later, just click the right mouse button on the Relationships dialog box and select the Show Table command.

In the Relationships dialog box, you have only the list of fields in the Customer information table and order Information table. How do you build relationships? In fact, the tables are made up of fields, and the relationships between the tables are also contacted by fields. With two fields in a different table connected, the other fields in the table can naturally be linked by the relationship between the two fields. That is to say, you can establish a relationship between the "order units" two fields in the company name and order Information table in the Customer information table. Select the company name item in the Customer Information table field list, and then hold down the left mouse button and drag to the Order unit item in the Order Information table, releasing the left mouse button, and the Edit Relationship dialog box appears on the screen.

This dialog can help us edit the relationship we have established, and the list box on the left can change the source of the two fields that establish the relationship. You can click the New ... button to create a new relationship, or click join type to select a join type for the join. Click the Join Type button, select the third item in the New dialog box that pops up, and then click OK. Go back to the Edit Relationship dialog box and click the Create button.

Now there is a "polyline" between the two list boxes that joins the "order Company" and "Company name" two options. Closes the Relationships dialog box and saves changes to the relationship layout. This is how you establish relationships between tables.

Relationships vs. Child data tables

Open the Customer Information table, you will find that the table added some new tables, they are "Customer Information table" Child table, that is, "Order Information table."

In this "One-to-many" scenario, the contents of both tables can be strung together through the company name information in the Customer information table. Each record in the main table is accompanied by an order record in a few child tables. A "child table" of each of the two tables with a "one-to-one" relationship.

Often after you establish a relationship between tables, Access automatically inserts child tables into the primary table. But these child tables are not displayed at the outset. In Access, let the child table be displayed, called the expand subdatasheet, so that the child table is hidden called "collapse" the subdatasheet. Expand the time to facilitate access to the order information, and folding up later there can be more convenient management of "Customer information table."

To expand a subdatasheet, just click in front of the first field in the primary table, and the child record for the corresponding record expands, and the "plus sign" in the small box in the grid becomes a "minus sign." If you click again, you can "fold" the child record of this lattice, and the "minus sign" in the small box is also changed back to "plus". If the main table is large, such a one-place "expand" and "collapse" subdatasheet is too cumbersome, and access provides a way for us to "expand" or "collapse" all the subdatasheet in the current datasheet. When you open a table with a subdatasheet, in the submenu of the Format menu subdatasheet item, there are three commands expand All, collapse all, and delete, expand all to expand all of the child data tables in the primary table, and the collapse All command collapses all the subdatasheet tables in the primary table Up When you do not need to display a subdatasheet in the primary table to reflect the relationship between the two tables, you can use the Delete command to delete the method that is displayed with the subdatasheet. However, the "relationships" of the two tables were not deleted. If you want to restore the form of "subdatasheet" on the primary table, you can open the main table, such as the Customer Information table, click the Subdatasheet command under the Insert menu, and then pop the Insert Subdatasheet dialog box as shown below:

Select table----Order Information table in the list box, and then select the Order Units field in the Link Child fields combo box. and select the Company Name field in the Link Master field combo box, and then click OK to reinsert the Child Datasheet order Information table in the Customer information table in the main datasheet Out. It is important to note that inserting a subdatasheet in any data table requires a "relationship" between two tables. If no connection has been established between the primary link field and the Child link fields for the two tables, insert the subdatasheet by clicking the OK button , you will be asked whether you want to establish a "relationship" between the two tables, and you can complete the Insert subdatasheet procedure only after you have established a relationship. Establishing a relationship between tables in this way is not as intuitive as the relationship established in the Relationships dialog box. So we advocate creating a "relationship" between tables in the Relationship dialog box, where Access automatically creates the subdatasheet.

Conclusion: This lesson shows the relationship between the table and the table through "Customer Information table" and "Order Information table". If you've mastered the table, start looking at the next lesson, and if you haven't fully mastered the table, look at the four lessons again, because tables are the most important part of the database.

Enforce Referential integrity

When we establish a relationship between tables, there is a check box on the window that enforces referential integrity, and after you click it, the Cascade Update related fields and cascade Delete related fields Two check boxes are available.

If the Cascade Update related fields check box is selected, when the parent row is updated (the related row in the left table of a pair of one or one pairs of relationships), Access automatically updates the Zikong (related row in the right table in a pair of one or one pairs of relationships), and when you delete the parent row, when you select Cascade Delete related fields, Zikong will also be deleted. And when "Enforce referential integrity" is selected, the "1" or "oo" symbol appears at both ends of the original polyline, and the "1" symbol in a one-to-one relationship appears near the two-table side of the polyline, and when a one-to-many relationship the "OO" symbol appears on one end of the corresponding polyline in the right table in the relationship.

Setting the Enforce referential integrity allows us to modify a record in the table without affecting the operation of the query. In particular, "Enforce referential integrity" can be more convenient when there are many tables and there are relational joins between tables.