Unit 7. Queries (V)


Multitable Queries

A multitable query is a query that obtains data from various tables and therefore needs to contain these various tables in the Tables area in the Design window.

To add a table to the Tables area we can (once we are in the Query design window):

drop down the Query menu and select the Show table option.

or,

Click on the button on the toolbar.

If the tables are not related or do not have any fields with the same name, the query obtains the concatenation from all the rows in the first table with all the rows from the second table, if we have a third table it will link up every one of the rows obtained in the first step with every one of the rows in the third table, and so successively. Basing the query on many tables, the result of the concatenation could achieve gigantic proportions!!

Furthermore, the majority of concatenations obtained normally are of no use to us and so we need to add a search criteria to select the rows that actually interest us. E.g I might be interested in data from the Students table and from the Courses table because I want to extract a list of courses with the course data and the name of all students of each course; in this case, I am not interested in linking the courses to every student, but rather linking it to its students; in this case we need to join the two tables.

Joining tables

 

In queries, we join two tables using a common field (or various) in the same way as we relate tables in the Relationships window by dragging the related field of one table over the another related field.

The tables are also joined automatically when a relationship exists between them.

When the tables are joined they appear in the following way in the design view:

When two tables are joined in a query, for every row in one of the table, Access directly searches in the other table for rows with the same value in the related field, and concatenate the two rows, so the query is more efficient.

This type of join operation is an internal join as all the values in the result are obtaindes from rows that exists in the tables joined.

With an internal join it will only obtain those rows that have at least one row in the other table that matches, lets look at an example:

In the Customers list mentioned earlier no customer without an assigned invoice will appear.

Well, in the case that we do want the rows without a matching row in the other table to appear as well, we use the External join.

The external join

 

The external join is used when we want the rows without a matching row in the other table to appear as well.

This type of relationship is defined in the following way:

Add the tables to the table area of the query.

Join the two tables by the related fields:

Double click on the line that relate the two tables

The Join properties dialogue box will appear

The join is internal by default (only the rows that have matching rows in the other table are included), if we want to define an external join we need to select option 2 or 3 depending on what we want to obtain.

If we select option 2,

,

the join will appear to us in the following way:

If we select option 3

,

the combination will appear to us in the following way:

The origin of the arrow indicates from which table we will obtain all of the records.

 

To practice you can perform the Step by step exercise in creating multitables.

 

Unit 7 exercise.

 

  Unit 7 evaluation test.

 



 

Page. 7.5

Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.