Navigation

Previous
Next
Homepage
FAQ
What's new
Sitemap

Topics

Access
Tables
Queries
Exercices on queries
Forms
Chart form
Reports
Label reports
Macros
Relations

Tutorials

Word
Excel

Excel 2007/2010/2013
Excel 2010
PowerPoint
Access

Others

Demonstration files
Texte en français

Contact

By e-mail
Join our Newsletter

Access - Relations

Introduction
Types of relations or the cardinality
What's required to make a relation
Create a relation on a query
Create a permanente relation between tables
What to check when Access refuses to create a relation

Before starting

This page includes exercises to experiment with the relations between tables. To help you in the understanding, you can copy the file demoacc2.mdb for those that have Access 97 or demoa2k2.mdb for those that have Access on 2000 of the page of the demonstration files. Click here to return you to this page. Copy the data base suited on a floppy disk or on your hard drive and return then to this page.

Introduction

The practical aspect to connect tables on fields in common is to avoid the redundancy. It's useless to rewrite several times the same information. For example, with a relational data base such as Access, it's useless to write several times the same information about a customer to every invoice. The force of the connected tables is to give you access to the information of another table than you would have no access without a relation between tables.

Let's take the example of tables Charges and Customers. The table Charges customer's number and no other information about the customer contains the field. But this field is the key to connect both tables. You can connect it with the field customer's number of the table Customers. Once connected, it's then possible to have the other information about the customer such as the name of the company, its address, it's persons contacts, its margin of credit etc.

The types of relations or the cardinalité

There are three types of relations: one to one, one to many and many to many.

it's not always easy to determine that kind of relation, so called cardinalité, that there is between two tables. It's necessary of the practice. I found a small trick to determine that kind of relation that there is between two tables. I found it accidentally by giving my demonstrations. I don't know how effective it's. But, it works very well during the demonstrations. It's a question of putting two questions.

1-For 1 (record of the first table), how many are there in the second table?
2-For 1 (record of the second table), how many are there in the first table?

Both questions always begin by: " For 1 of this, how much it? " The only acceptable answers are one or many. When you have the answers, place them in the small picture that follows. It's necessary to invert the second question to enter it the picture. Replace the "?" with the results (one or many).

Then, you write on the last row the biggest, among one and many, of the second and the last column. You can then know about that kind of relation that it is a question.

The next exercise consists in experimenting with this small trick. It's necessary to determine that kind of relation that he can there have between tables Invoice and Customers. It's necessary to ask the questions.

Q1: For 1 invoice, how many customers can I have? The answer is one. An invoice, a customer.
Q2: For 1 customer, how much invoices can I have? The answer is many. At least, you hope for it. It's not practical to have a customer who only comes once! Enter the information as the picture below.

Effectively, it's indeed a relation of One to Many of the Customers towards Invoice. You can now try with the other tables or the other situations.

This means as well as you can find only once the information in common in one of the tables and several times in another. For this example, every customer's number in the Customers's table is unique. There are two records (or customers) with the customer's same number. However, in the table Invoices, you can find the same invoice number for several different records. It is for that reason that the relation is called 1 to many. The information in common meets itself only once in the first table (Customers) and potentially several times in the second (Invoices).

ONE TO ONE RELATION

Ex: A man is married to a single woman. A woman is married to a single man.
I should remind you that the bigamy is illegal!

ONE TO MANY RELATION

Ex: A person can possess several motorcycles. A motorcycle can be possessed only by a single person. (Let's forget about co-ownership casses for now).

MANY TO MANY RELATION

Ex:. A student follows several classes. A class is followed by several students

You must pay attention during the conception of a database to the " many to many " relationships. There are more details farther of this Web page on this special box.

What is required to make a relation

2 tables (or queries or a combination of both)
1 field in common in each of the tables.
The same type of field (Text with text, numeric with numeric...)
The same length (no field 15 characters long can go with another that's 50 characters long!)
The same kind of information (Ex: Code of inventory with codes of inventories, SSN with SSN...)

Do not try to create a relation between the fields Date and Phone number. Both fields are not the same types (Text, Number, Date/Time, Yes/No, AutoNumber...) Also, don't try between fields name and First name. Although both fields are the type "text", they hold the same kind of information.

There is however an exception that confirms the rule. You can create a relation between a field of type AutoNumber and a Number type the size of the field of that is " Whole length ". It would be impossible to create a " one to many " relation if both fields where AutoNumber. By definition, this type of field can't have twice the same information!

This exception is very advantageous. For example, the user does not need to worry which ID number to give to a new customer. Access take care of it automatically.

Create a relation in a query

it's very advantageous at times to be capable of showing the information contained by several tables at the same time. It's however necessary to have a relation between tables to have an interesting result. The next exercise consists in creating a relation between two tables in a query.

From the main menu, press the the queries tab.
Press the New button.
Of the list of the available tables, add tables Invoice and Customers.
Select the following fields of the table Charges: invoice number and Codes customer.
Select the following fields of the table Customers: Name and Address.
Execute the query.

What you see is the result of all the possible combinations between both tables. notice that for every invoice number there are 5 customers! In fact, there are 14 records in the table Charges and 5 records in the table Customers. So, there is 14 * 5 ( 70 ) records of activated. This is really impossible. The reason is that there is no relation between these two tables at the moment. If you don't make relations between the tables of a query, Access will show all the combinations of possible records between both tables. It's necessary to create a relation between two tables to avoid this kind of situation.

To turn(return) to the mode creation:

Press the button.
OR
Of the View menu, select the option Creation.

determine fields in common between both tables.

In that box, it's about fields Customer's number Codes customer and. Although they have no same name, they are the same types of fields. They have also the same size(format) or of length and contain the same kind of information.

Place the pointer over one of the fields in common.
By keeping your finger on the left mouse button, move the pointer over the other field in common.
Release the mouse button..

A thin row should appear between both fields. Both tables are now connected. Otherwise, re-try.

it's also possible, accidentally , to have connected the fields that have no same kind of information. It's necessary to remove the bad relation before being able to retry.

Click the row that connects both tables.
Of the Edit menu, select the Delete option.
OR
Press the Delete key of the keyboard.

When tables are correctly connected, it's time to show the result.

Execute once again the query by pressing on the button .

The number of records should have reduced to 14 invoices. It's necessary to notice that you see the information of two tables at the same time! it's there one of the big advantages of the relational data bases; be capable of reaching the information that no table, taken only, is capable of supplying. It's only once that these tables are connected that they are really effective.

The mechanism of a relation

From a query, it's possible to change or to add records. As you write a value in one of the fields in common. Access will try to find if this value exists in one of the records of the other connected table. If it finds, you will have access to the contents of all the fields of the record. Let's take the example of tables Charges and Customers that are connected on fields in common customer in a query Codes customer and No.. If you write a number between 1 and 5, Access can find a record in the table Customers and show you all the information of the record if you want it.

You can enter or to change of the information from a query that contains several tables. It's necessary to make sure that one of the fields in common of all the connected tables is used in the query. Otherwise, Access will warn you that he can not realize the query.

Be careful!
Furthermore, this field in common should be the one that you can enter several times the same information. For example, if you make a relation between tables Invoice and Customers on the field in common customer's number, use the field Codes customer of the table Charges because you can enter several times customer's same number invoices. When you entered the information the table Customers, you can not bring in it second time!

Creating a permanent relations between tables

it's advantageous to connect tables in the query to reach the information that none of the only taken tables possesses. The problem with this technique is that you must redo the relations every time you make(do) a new query. Access offers you a way of creating "more permanent" relations between the tables of your data base. It has also the advantage to verify " the integrity " of the relations between tables.

From the main menu, it's possible to reach the relations section.

Press the button.
OR
Of the Tools menu, select the option Relation.

it's in this window that you go to create the relations between tables. When you open this section for the first time, there are no tables; only a big vacuum. It's first necessary to add tables or queries on that you want to connect. To accelerate the demonstration, I have already put for you all the tables that will be necessary for the relations except the table Customers.

Add a table

When to go to open to you the area of the relations for the first time, it should be empty. It's necessary that to add you tables or queries that you want to connect before establishing the relations.

Of the Relations menu, select the option View the table.
OR
Press the button .
OR
Place the pointer of the mouse in the area of the relations.
Press the right mouse button.

Of the context menu, select the option View the table.

The following dialog box will appear.

With Access, it's possible to connect tables or queries if they have a field in common.

Select the Tables tab.

Then, select the tables of that you want to connect. For the demonstration,

select the table Customers and press the button Add.
Because there are no more other tables to be added, press the button Close.

Establish the relations between tables.

Place the pointer over the field Customer's number of the table Customers.
Click on the left mouse button to select the field.
While pressing the left mouse button, move the pointer over the field Codes customer.
Release the mouse button.

When you will have made the relation, the following screen will appear.

Apply the repository integrity

This option is necessary to establish a relation that I call "strong". It's the relation that makes sure that there is a value in the other connected table. By activating the option " to apply the repository integrity ", Access makes sure that certain integrity rules of a relation are respected. It's so that the relations between the connected tables are always good. No information can be "lost" by letting Access the right validate these rules.

Access can refuse to create a relation with integrity repository for these reasons:

The table that contains the unique record has no option of index without doubloons or primary key.
The table that contains the unique record has a double. Ex: several products have the same product number.
The one of records of one of tables connected with one of the common fields empties. it's always necessary that fields in common both tables have of the information in the other table.
The value written in the table that can contain several times the same value in the field in common has no equivalent in the other table. Ex: one of the records contains the number of product number 5 although there are only four products, from 1 to 4 , of available.
Another reason is that one of the tables is " opened " or shown. It's necessary to close any tables, queries, reports and forms before creating the relations between tables. Here is the procedure to be followed to close objects that would have remained opened.

Close the window of the relations.
From the Window menu, select the object (table, query...) that remained opened.

The option of the opened menu allows you to pass easily from an object to another. In that box, it's to close all the objects opened except the data base and the screen of the relations.

Close the object.
If the other objects are opened, except for the data base and the screen of the relations, repeat the previous operations to close these objects.

You can then re-try to create relations between tables.

The Updated option cascades to it.

Updates in one or several other tables that are connected. If you change customer's number, all the records of the other connected tables customer of that the code is identical are also to be modified. Ex: If you change the value of the field No. Customer of tables Customers, Access goes changed for all the fields in common connected tables. In that box, would only be the field Codes Customer of the table Charges.

The Delete in cascade option.

This option erases the records of the other table that have the same field in common as the killed(abolished) record. When should one activate this option? It depends on the contents of tables. In that box, for fiscal reasons, it would be preferable to keep the data even if the customer leaves. But for another data base, for bookings for example, it would be preferable to remove the options at the same time as a booking.

Here is a chart with a representation of tables and relations from them. A little later, one will ask you to create all the relations between these tables. For the moment, read the other possible options on the relations.

Delete a relation.

You can remove one relations on tables. There are many reasons to remove a relation between tables or queries. The first is that you made an error at the time of the creation of the relation. You chose accidentally the bad field at the time of the creation of the relation. Or, after a more detailed analysis, you change the relations between tables. So, to allow you to change the structure of a connected table, he can be necessary before to remove a relation. Access goes will say it to you if it's the box.

Click on the row that connects both tables.
From the Edit menu, select the Delete option.
OR
Press the Delete key.

Modify the options of a relation.

Click on the row that connects both tables.
Double-click on this row.
OR
Of the Relations menu, select the option Modify a relation.

The options of the relation are going then to appear. You can modify them and then press the button to create to keep these modifications.

It's now time to create the relations between tables. Here is the particular box who could indeed arrive at you at the time of the creation of relations between the tables of your data base.

Creating a relation between tables Charges and Employees.

it's first necessary to determine that type of relation that it's a question or its cardinalité.

1 invoice is generated by a single salesperson.
1 salesperson can generate several invoices.

it's about a relation of type 1 to many.

it's now necessary to create the relation between tables Employee and Invoice on their field in common.

Place the pointer over the field Numéro of poste (Employee's ID number) of the table Employees.
Click on the left mouse button to select the field.
While pressing the left mouse button, move the pointer over the field No. salesperson.
Release the mouse button.

it's now necessary to choose the options.

Activate the option Apply the integrity repository.
Make sure that the relation is 1 to many.
Press the button Create.

The relation will not work!

What to check when Access refuses to create a relation.

As being mentioned above, the relation between tables Charges and Employees will not work. The contents of certain record on the field in common (No. salesperson and number of occupation) don't respect the integrity rules. To warn to you of it, Access will show you the following message.

Read attentively the message to understand what happens. In that box, it's the second sentence that explains the situation: " The data of the table " Facture " don't respect the rules of repository integrity ". And it's true. Both last records of the table have no valid contents in the field No. salesperson. it's for that reason that Access refuses to create a permanent relation between these two tables.

For this example, you know the cause of the problem. It's enough to put values suited for these two records to solve the problem. But what to do when you don't know the cause of the problem? Access has an indispensable tool to offers to make sure that you have good relations between your tables and/or queries. it's about the query of not correspondence. The next part consists in creating a query of not correspondence that determines the cause of the problem between tables Charges and Employees.

Close the area of the relations by selecting of the menu File the option Close.
Click on the the queries tab.
Press the New button.

From the list of the possible queries, choose Assisting Request of not correspondence.
Press the OK button.

To determine the records that don't correspond between both tables, it's necessary first to indicate to Access the name of both tables to connect follow-up of the name of fields in common of both tables.

it's necessary to indicate to Access the name of the first banks to connect that causes a problem of relation.

From the list of tables, select the table Charges.
Press the Next button >.

it's now necessary to indicate the name of the second table that causes a problem of relation.

From the list of tables, select the Employees table.
Press the Next button >.

it's then necessary to show to Access that are fields in common of both tables.

From the table Charges, click the field No. salesperson.
From the Employees table, click the field Numéro of poste (Employee's ID number).
Press the button.
Press the Next button >.

Access asks you then that are the fields that you want to see in the query. For the purpose of the example, select all the fields.

Press the button .
Press the Next button >.

To end, Access asks you for the name to look to the query. For the moment, don't change the name of the query.

Press the button End.

Access shows the result of the query.

Invoices 21 and 22 have no valid values in the No vendeur (salesperson's ID number) field. The invoice number 21 has an employee number (No vendeur) that's impossible because there are only 7 employees at present in the company. For invoice number 22, there is no salesperson's number. A relation is impossible if the contents of a field in common is empty.

For both invoices, enter salesperson's number 7 the field No. salesperson.

After this correction, it's now possible to you to create a "permanent" relation between these two tables.

Close the query.
Return to the section of the relations.
Redo the relation between tables Employees and Invoice on their field in common (Numéro of poste (Employee's ID number) and No. salesperson).

Both tables are now connected on a field in common. You can now create the relations between the tables of this data base such as in the previous chart.

The Many to Many relations

Access offers you a way easy to create relations of type 1 to 1 and 1 to many with the option rules of integrity. However, the creation of relations of type many to many require more work. Let's take the example of the creation of a relation between tables Charges and Produced. Above all, you must determine of that kind of relation that it's a question.

1 invoice can contain several products.
1 product can be contained in several invoices.

it's indeed a relation of many to many.

it's impossible to directly make a many to many relation. It's necessary to create an intermediate table that contains the primary key of both tables. It's then necessary to create two relations of type 1 to many between tables Charges, Product and the intermediate table by using the field of the primary key of both tables.

Ex:

don't forget to activate the option rules of integrity and also the option of update in cascade. Otherwise, the relation of many to many is more difficult. So that a relation works, it's necessary that there is the same kind of information in both connected tables. The option " To update in cascade " makes sure that the information meets itself also in the other table. It's so useless to you to worry to have you the information in both tables. Access take charge of it for you.

Go to of the screen of the relations

If you want to keep the relations between tables, you should first protect them before leaving the mode of relations.

Click on the button with the yellow floppy disk .
From the File menu, select the option Close.

Creating an invoice

it's here that all the relations between tables will be put in advantage.

Creating a query that contains all the connected tables.

Click on the the queries tab.
Press the New button.
Select of the list of tables: Invoice, Transition Fact-Inv, Inventory, Customers and Employees.
Press the button Close.

Every time to add to you a table in the query, the table adds as well as its relations with the other tables. Furthermore, it's about relations with the options of repository integrity. The first relation was a "simple" relation; without the possibility of a relation with repository integrity.

From the list of the possible fields, select in order the following fields:

invoice number (Transition Fact-Inv), Codes customer Facture (Invoice), No. salesperson Facture (Invoice), Number of product (Transition Fact-Inv), Quantity (Transition Fact-Inv), Unit price (Inventory).

it's the necessary minimum of fields to have a query with relations that work. it's necessary that one of the fields in a relation is shown. Furthermore, this field should be the one where you can enter the same information several times. You can add the other fields to your choice. Because they are connected, all the information is going automatically to display without having to add anything of the information.

To demonstrate you the advantage to connect tables, the last field to be added is a calculated field. It's going to take the information of two various tables (Quantity and Unit price) to find the total of the deal. Because it's possible to find the total from the information of tables, it's useless to have a "Total" field in one of the tables.

Place the cursor in the box empties in the right-hand side of the field Unit price.
Write the following text: Total: [Quantity] * [Unit price]

As for all the calculated fields, you must give it a name (Total). It's then necessary to separate the name of the formula by writing one ":". It remains that to write the formula. This field will give the total of each of the items of the invoice.

Data entry for an invoices

Having to complete the previous operations for the creation of relations between the various tables of the data base, it remains to seen how to enter many invoices and to explain the working of the chosen options. To enter the data,

Press the button.
OR
From the View menu, select the option Banks of data.
Enter no information the field invoice number and press the Enter key.

As soon as the cursor is moved to the following field, a number appeared in the first field. Why?

it's because of the relation between this field and that of No. Invoice of the table Charges. Not only is the relation of a " 1 to many " type, but there is also an option " To update in cascade ". It's this option that forces Access to see the contents of the field No. invoice number charges and to copy it in the field. No need to enter the information every time to begin you a new invoice.

Enter the information for the other fields your choice.

You see that it's possible to enter of the information several tables at the same time. So, the advantage of the relations because there are redundancy of information only for fields in common various tables. It's useless to rewrite the information about the customer or about the product because Access has access to this information thanks to the relations between tables.

The next exercise consists in adding a second item to the same invoice.

In the field invoice number, enter the same invoice number of the previous record.
Enter the information for the other fields your choice.

And so it's possible to add several items to the same invoice. You can use this technique for the other tables that have a relation of " 1 to many " type.

I would be very happy to receive your comments and your criticisms on this page, or quite other page of this site.