Microsoft access tutorial relationships

Here, on the left-hand side of each and every record, you will see a little plus sign by default. When you create a relationship, Access will automatically add a sub-datasheet to that table. Let us click on the plus sign and you will see the information that is related to this record is on the tblHRData table. Click on the Save icon and open tblHRData and you will see that the data we have entered is already here.

Pavan Lalwani. Saatya Prasad. Gowthami Swarna. 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.

A better solution is to store the publisher's information only one time, in a separate table that we will call "Publishers. 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. There are three kinds of relationships between tables. The kind of relationship that is created depends on how the related columns are defined.

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. A one-to-many relationship is created if only one of the related columns is a primary key or has a unique constraint. In the relationship window in Access, the primary key side of a one-to-many relationship is denoted by a number 1.

The foreign key side of a relationship is denoted by an infinity symbol. 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.

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. This kind of relationship is not common, because most information that is related in this manner would be in one table.

You might use a one-to-one relationship to take the following actions:. In Access, the primary key side of a one-to-one relationship is denoted by a key symbol. The foreign key side is also denoted by a key symbol. 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. Even when both matching fields are Number fields, they must have the sameFieldSizeproperty setting. A junction table is one that contains common fields from two or more other tables within the same database. It is used as a reference table in a many-to-many relationship such as we are doing in our example.

Junction tables are known under many different names. Here are some: cross-reference table , bridge table , join table , map table , intersection table , linking table , many-to-many resolver , link table , pairing table , pivot table , transition table , or association table.

So if you ever hear someone mention one of those, you should have some idea what they are referring to. We already know that a primary key ensures that the data in the field is unique.

This is important because our Orders table needs a unique value in order to reference any record from the other tables. For example, it can't use the FirstName field because there could be more than one person with a given first name.

And it can't use the DateCreated value because it's possible that two records could be created at exactly the same time eg, if two operators are entering data or if many records are imported from an external source. This is why we need at least one field that we know will only ever contain a unique value. A foreign key is simply the primary key's corresponding field in the related table.

So in our example, the CustomerId field in the Orders table is a foreign key, while the CustomerId field in the Customers table is a primary key.