Step by step exercise. Multitable queries


Objective.

 

Practice the operations for Creating, executing, and saving a multitable query.

 

Exercise 1.

 

We will create a query from two related tables. The query will contain data like student surname, city, and the name of the course that the student is attending, but only of those students from Los Angeles or those taking a Spanish course.

1 Open the Classes.mdb.

2 Position yourself over the Queries tab of the Database window.

3 Click on Create Query in Design view.

Next the Show table dialogue box will appear.

4 Double-click on the Courses table. Observe how the Courses table now appears in the tables area.

5 Double-click on the Students table.

6 Click on the Close button in order to close the dialogue box.

Observe how the tables appear combined as they have a defined relationship in the Relationships window. As they are united by the Course code field, the result will be formed a record with the student together with the course that the student is attending.

7 Double-click on Student Surname, City and Course name fields in order to include them in the QBE grid.

8 Click on the Run button to see the result.

Observe each student appears with the name of the course is attending.

9 Click on the Design button to return to the QBE grid and fill in the QBE grid the way shown below:

 

Observe we have put the criteria in different rows because we want those students that live in Los Angeles OR that attend a Spanish course.

10 Click on the Run button to see the result.

11 Close the query, and as this is the first time that you save it you will be asked for a name, type Spanish or Los Angeles.

Ejercicio 2.

 

We will now create a query to know the data of all the students that are in the Students table and the name of the course that they are recorded in.

1 Open the Classes.mdb.

2 Position yourself over the Queries tab of the Database window if you are not already there..

3 Click on Create Query in Design view.

Next the Show table dialogue box will appear.

4 Double-click on the Courses table. Observe how the Courses table now appears in the tables area.

5 Double-click on the Students table.

6 Click on the Close button in order to close the dialogue box.

7 Fill in the QBE grid so that it remains this way:

With the first column we indicate that we want all the Students fields, and in the second column the name of the course assigned to the student.

8 Click on the run button to see the result.

Observe how not all of the students appear, students 4, 5, 6, and 7 are missing because these students have no value in the Courses field. Only the students that have an existing Course in the Courses table will appear.

9 Click on the Design button to return to the QBE grid.

We will now change the query so that all the students will appear.

1 Double click on the line that relate the two tables in the tables area. The Join properties will open.

2 Select the Include ALL records from 'Students' and only those records from 'Courses' where the joined fields are equal.

3 Click on the OK button to close the dialogue button.

Observe how the relationship now appears as an arrow pointing the Courses table, this indicates to us that the query includes all the Students records.

4 Click on the run button to see the result.

Observe how students 4, 5, 6, and 7 now appear, but with no course name as they have no course.

5 Save the query with the name All the Students but do not exit from it.

6 Click on the Design button to return to the QBE grid.

We will now create a query from the last one in order for all the courses to appear as well as the student assigned to each course.

1 We are still in the All the students query.

2 Drop down the File menu and select the Save as... option.

3 Give our new query the name All courses.

In this way we have created a new query from the last one and we are in this one (look at the title bar).

4 Double click on the relationship that relates the two tables in the table area. The Join properties dialogue box will open.

5 Select the Include ALL records from 'Courses' and only those records from 'Students' where the joined fields are equal.

6 Click on the OK button to close the dialogue button. Observe how the relationship now appears as an arrow pointing the Students table, this indicates to us that the query includes all the Courses records.

7 Click on the run button to see the result.

Observe how the courses that have no students also appear now.

8 Save the query clicking on and closing it.

 



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.