In a relational database, relationships enable you to prevent redundant data. For example, if you are designing a database that will track information about books, you might have a table named "Titles" that stores information about each book, such as the book's title, date of publication, and publisher. There is also information that you might want to store about the publisher, such as the publisher's telephone number, address, and ZIP Code/Postal Code. If you were to store all this information in the "Titles" table, the publisher's telephone number would be duplicated for each title that the publisher prints.
access relationship between table and query
To make sure that you data stays synchronized, you can enforce referential integrity between tables. Referential integrity relationships help make sure that information in one table matches information in another. For example, each title in the "Titles" table must be associated with a specific publisher in the "Publishers" table. A title cannot be added to the database for a publisher that does not exist in the database.
A relationship works by matching data in key columns, usually columns (or fields) that have the same name in both tables. In most cases, the relationship connects the primary key, or the unique identifier column for each row, from one table to a field in another table. The column in the other table is known as the "foreign key." For example, if you want to track sales of each book title, you create a relationship between the primary key column (let's call it title_ID) in the "Titles" table and a column in the "Sales" table that is named title_ID. The title_ID column in the "Sales" table is the foreign key.
A one-to-many relationship is the most common kind of relationship. In this kind of relationship, a row in table A can have many matching rows in table B. But a row in table B can have only one matching row in table A. For example, the "Publishers" and "Titles" tables have a one-to-many relationship. That is, each publisher produces many titles. But each title comes from only one publisher.
In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table that is called a junction table. The primary key of the junction table consists of the foreign keys from both table A and table B. For example, the "Authors" table and the "Titles" table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the "TitleAuthors" table. The primary key of the "TitleAuthors" table is the combination of the au_ID column (the "Authors" table's primary key) and the title_ID column (the "Titles" table's primary key).
In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.
When you create a relationship between tables, the related fields do not have to have the same names. However, related fields must have the same data type unless the primary key field is an AutoNumber field. You can match an AutoNumber field with a Number field only if theFieldSizeproperty of both of the matching fields is the same. For example, you can match an AutoNumber field and a Number field if theFieldSizeproperty of both fields isLong Integer. Even when both matching fields are Number fields, they must have the sameFieldSizeproperty setting.
If you have not yet defined any relationships in your database, the Show Table dialog box is automatically displayed. If you want to add the tables that you want to relate but the Show Table dialog box does not appear, click Show Table on the Relationships menu.
Referential integrity is a system of rules that Access uses to make sure that relationships between records in related tables are valid, and that you do not accidentally delete or change related data. You can set referential integrity when all the following conditions are true:
For relationships in which referential integrity is enforced, you can specify whether you want Access to automatically cascade update or cascade delete related records. If you set these options, delete and update operations that would usually be prevented by referential integrity rules are enabled. When you delete records or change primary key values in a primary table, Access makes the necessary changes to related tables to preserve referential integrity.
If you click to select the Cascade Update Related Fields check box when you define a relationship, any time that you change the primary key of a record in the primary table, Microsoft Access automatically updates 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 that customer's orders so that the relationship is not broken. Access cascades updates without displaying any message.
If you select the Cascade Delete Related Records check box when you define a relationship, any time that you delete records in the primary table, Access automatically deletes related records in the related table. For example, if you delete a customer record from the "Customers" table, all the customer's orders are automatically deleted from the "Orders" table. (This includes records in the "Order Details" table that are related to the "Orders" records). When you delete records from a form or datasheet when the Cascade Delete Related Records check box selected, Access warns you that related records may also be deleted. However, when you delete records by using a delete query, Access automatically deletes the records in related tables without displaying a warning.
Option 1 defines an inner join. An inner join is a join in which records from two tables are combined in a query's results only if values in the joined fields meet a specified condition. In a query, the default join is an inner join that selects records only if values in the joined fields match.
Option 2 defines a left outer join. A left outer join is a join in which all the records from the left side of the LEFT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the right side.
Option 3 defines a right outer join. A right outer join is a join in which all the records from the right side of the RIGHT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the left side.
After you have created a table for each subject in your database, you have to give Access a way to bring that information back together again when needed. You do this by placing common fields in tables that are related, and by defining relationships between your tables. You can then create queries, forms, and reports that display information from several tables at once. For example, the form shown here includes information drawn from several tables:
To represent a many-to-many relationship, you must create a third table, often called a junction table, that breaks down the many-to-many relationship into two one-to-many relationships. You insert the primary key from each of the two tables into the third table. As a result, the third table records each occurrence, or instance, of the relationship. For example, the Orders table and the Products table have a many-to-many relationship that is defined by creating two one-to-many relationships to the Order Details table. One order can have many products, and each product can appear on many orders.
In a one-to-one relationship, each record in the first table can have only one matching record in the second table, and each record in the second table can have only one matching record in the first table. This relationship is not common because, most often, the information related in this way is stored in the same table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. When you do identify such a relationship, both tables must share a common field.
You can create table relationships explicitly by using the Relationships window, or by dragging a field from the Field List pane. Access uses table relationships to decide how to join tables when you need to use them in a database object. There are several reasons why you should create table relationships before you create other database objects, such as forms, queries and reports.
To work with records from more than one table, you often must create a query that joins the tables. The query works by matching the values in the primary key field of the first table with a foreign key field in the second table. For example, to return rows that list all of the orders for each customer, you construct a query that joins the Customers table with the Orders table based on the Customer ID field. In the Relationships window, you can manually specify the fields to join. But, if you already have a relationship defined between the tables, Access supplies the default join, based on the existing table relationship. In addition, if you use one of the query wizards, Access uses the information it gathers from the table relationships you have already defined to present you with informed choices and to prepopulate property settings with appropriate default values.
When you design a form or report, Access uses the information it gathers from the table relationships you have already defined to present you with informed choices and to prepopulate property settings with appropriate default values. 2ff7e9595c
Comments