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. It is possible to connect it with the field customer's number of the table Customers. Once connected, it is 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 is not always easy to determine that kind of relation, so called cardinalité,
that there is between two tables. It is 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 is. But, it works very well during the demonstrations. It is 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 image that follows. It is necessary to invert the second question
to enter it the image. Replace the "?" with the results (one or many).
| Determine the type of relation |
|
Table 1 |
|
Table 2 |
| Q1: |
1 |
- > |
? |
| Q2: |
? |
<- |
1 |
| Max: |
|
|
|
Then, you write on the last line 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 is necessary
to determine that kind of relation that he can there have between tables Invoice and Customers. It is necessary to ask the questions.
Q1: For 1 invoice, how much have I of customers? The answer is one. An invoice, a customer.
Q2: For 1 customer, how much may I have of invoice? 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 image below.
| Determine the type of relation |
|
Invoice |
|
Customers |
| Q1: |
1 |
- > |
1 |
| Q2: |
Some |
<- |
1 |
| Max: |
Some |
|
1 |
Effectively, it is 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 the other one. 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 number of invoice 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
It is necessary to pay attention during the conception of a database with relations
of type " many to many ". There are more details farther of this Web page on
this special case.
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 long 15 characters with one the other long 50 characters!)
The same kind of information (Ex: Code of inventory with codes of inventories, NAS with NAS...)
Do not try to create a relation between the fields Date and Phone
number. Both fields are not the same types (Text, Numeric, Date/Time, Yes/No,
NuméroAuto...) Also, don't try between fields name and First name.
Although both fields are the type "text", they have no same kind of information.
There is however an exception that confirms the ruler. It is possible to make(do) a relation between a field of type NuméroAuto and a field of numeric type the size of the field of that is " Whole length ". It would be impossible to create a relation of type " one to many " if fields in common are of type NuméroAuto. By definition, this type of field can not have twice the same information!
This exception is of size and very advantageous. For example, the user does not need to worry to give numbers of identifications to a new customer. Access take care of it automatically.
Create a relation on a query
It is very advantageous by moments to be capable of showing the information contained by several tables at the same time. It is 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: Number of invoice 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 number of invoice 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 is 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 case, it is 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 line should appear between both fields. Both tables are now connected. Otherwise, re-try.
It is also possible, accidentally , to have connected the fields that have
no same kind of information. It is necessary to remove the bad relation before
being able to retry.
Click the line 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 is necessary to notice that you see the information of two tables at the same time! It is 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 is only once that these tables are connected that they are really effective.
The mechanism of a relation
From a query, it is 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 recording. 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 recording in the table Customers and show you all the information of the recording if you want it.
It is possible to enter or to change of the information from a query that contains several tables. It is 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 is 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 it is necessary to 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 is possible to reach the relations section.
Press the  button.
OR
Of the Tools menu, select the option Relation.
It is 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 is 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 zone of the relations for the first time, it should be empty. It is 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 zone of the relations.
Press the right mouse button.
Of the context
menu, select the option View the table.
The following window will appear.
With Access, it is 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 is 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 is 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 recording has no option of index without doubloons
or primary key.
The
table that contains the unique recording 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 is 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 is 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 case, it is 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 case, 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) recording. When should one activate this option? It depends on the contents of tables. In that case, 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.
It is possible to 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 is the case.
Click on the
line 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
line that connects both tables.
Double-click
on this line.
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 case 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 is first necessary to determine that type of relation that it is a question or its cardinalité.
1 invoice is generated by a single salesperson.
1 salesperson can generate several invoices.
It is about a relation of type 1 to many.
It is 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 is 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 recording 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 case, it is
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 is 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 is 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 is 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 zone 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 is 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 is 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 is 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 is 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 is 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 relations of many to many
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,
it is necessary to determine of that kind of relation that it is a question.
1 invoice can contain several products.
1 product can be contained in several invoices.
It is indeed a relation of many to many.
It is impossible to directly make a many to many relation. It is necessary
to create an intermediate table that contains the primary key of both
tables. It is 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:
| Invoice |
Relation |
Transition Fact-Inv |
Relation |
Inventory |
| No. Charge |
1 - > many |
Number of invoice |
|
|
|
|
Number of product |
Many< - 1 |
Number of inventory |
|
|
Bought quantity |
|
|
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 is 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 is 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 is 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 is 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:
Number of invoice (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 is the necessary minimum of fields to have a query with relations that work.
It is 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 is possible to find the total from the information of tables, it is 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, it is necessary to give it a name (Total). It is 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 Number of invoice 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 is 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 is this
option that forces Access to see the contents of the field No. Number of invoice
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 is 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 is 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 Number of invoice, enter the same number of invoice of the previous recording.
Enter the information for the other fields your choice.
And so it is 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.
|
