Share this page
Access - Relations
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.
The practical aspect to connect tables on fields in common is to avoid the redundancy. It is useless to rewrite several times the same information. For example, with a relational data base such as Access, it is 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. 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.
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?
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 is 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 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.
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.
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 box.
2 tables (or queries or a combination of both)
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.
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.
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.
determine fields in common between both tables.
In that box, 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.
A thin row 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 row that connects both tables.
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.
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.
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 area 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.
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.
Establish the relations between tables.
Place the pointer over the field Customer's number of the table Customers.
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:
table that contains the unique recording has no option of index without doubloons
or primary key.
the window of the relations.
The option of the opened menu allows you to pass easily from an object to another. In that box, it is to close all the objects opened except the data base and the screen of the relations.
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) recording. 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.
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 box.
Click on the
row that connects both tables.
Modify the options of a relation.
Click on the
row that connects both tables.
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 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.
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.
It is now necessary to choose the options.
the option Apply the integrity repository.
The relation will not work!
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 box, 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 area of the relations by selecting of the menu File the option Close.
From the list of the possible queries, choose Assisting Request of not correspondence.
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.
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.
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.
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 .
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.
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.
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.
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 .
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.
on the the queries tab.
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.
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,
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.
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.
|You like what you read?
Share it with your friends.