Creating a Multiple-Table Query

You can manually join

A query in Design view.

In Access you will often need to look at and analyze information that comes from not one but several different tables. Since Access is a relational database, it's easy to establish a relationship between two or more tables and look at the information that goes together.

Just like it sounds, a multiple-table query blends together information from two or more related tables. Working with a multiple-table query usually isn't much different from working with a single-table query. You tell Access which tables you want to use in your query and specify the fields and criteria you want to see. The main difference between a multiple-table query and a single-table query is that with , Access creates a link between related tables. When the query is displayed in Design view, this link (called a join) appears as a line that connects two or more tables.

When you create a multiple-table query, Access will usually link or join the tables automatically. Sometimes, however, you will have to manually join two tables in the query design window. You can manually join two tables by dragging a field from one table's field list to the matching field in the other table's field list, as shown in figure. If the tables don't have any fields in common, you must add another table to act as a bridge between them. This lesson will give you some experience creating a multiple-table query.

1. Open the Lesson 6 database.

   You should know how to create a query by now...

2. Click the Queries icon in the Objects bar and then double-click Create query in Design view.

   The query design window and Show Table dialog box both appear. You have to select the tables and/or queries you want to use.

3. Click the tblEmployees table and click Add.

   A field list for the tblEmployees table appears in the top half of the query design window. You also want to add the tblCustomerTours table and the tblTours table to the query.

4. Add the tblCustomerTours table and tblTours table to the query.

   When you have finished adding the tables and/or queries to your query, you can close the Show Table dialog box.

5. Click Close.

   If two tables are related, Access will automatically connect their common fields with a join line. For example, Access automatically joined the tblTours table and the tblCustomerTours table because they are already related. If the tables aren't related you will have to manually join the tables by dragging a field from one table's field list to the matching field in the other table's field list.

   You need to connect the tblEmployees table with the tblCustomerTours table.

6. Click EmployeeID in the tblEmployees field list and drag and drop it onto Employee in the tblCustomerTours field list.

   Click EmployeeID in the tblEmployees field list and drag and drop it onto Employee in the tblCustomerTours field list.

   You can remove a join from a query by clicking the join line (carefulthere's not much there to click!) and pressing Delete.

   Next you need to specify the fields you want to appear in the query results. You can add fields to the query design grid in two ways:

   • By double-clicking the field in the field list.

   • By clicking and dragging the field down to the design grid yourself.

   Because field lists don't have much room to display their contents, you may have to scroll up or down the list in order to find the field you want.

7. Double-click the LastName and FirstName fields in the tblEmployees field list.

   Access adds the LastName and FirstName fields from the tblEmployees table to the design grid. Next add the fields for the tblTours and tblCustomerTours tables.

8. Double-click the TourName field in the tblTours field list and the Date and Cost fields in the tblCustomerTours field list.

   Next you need to specify any criteria for the query. For this exercise, you want to see tours from the first quarter of the year.

9. Click the Date column's Criteria row and type Between 1/1/00 and 3/31/00.

   You want to sort your query by date, so...

10. Click the Date column's Sort box list arrow and select Ascending.

    That's it! You've just created a query, as shown in figure.

11. Save the query as qryFirstQuarterTours and click OK.

    Let's run our new query!

12. Click the Run button on the toolbar.

    Other ways to run a query are to open the query from the Database window, or click the View button on the toolbar in Design view, or select Query » Run from the menu.

    Access displays the results of the query. The results show the names of the employees who sold tours and the names, dates, and costs of each tour.

TO CREATE A MULTIPLE-TABLE QUERY IN DESIGN VIEW:

1. CLICK THE QUERIES ICON IN THE OBJECTS BAR, THEN DOUBLE-CLICK CREATE QUERY IN DESIGN VIEW.

2. CLICK THE SHOW TABLE BUTTON IF NECESSARY. SELECT THE TABLE OR QUERY YOU WANT TO USE AND CLICK ADD.

3. REPEAT STEP 2 AS NECESSARY FOR ADDITIONAL TABLES OR QUERIES. CLICK CLOSE WHEN YOU'RE FINISHED.

4. IF ACCESS DOESN'T AUTOMATICALLY JOIN THE TABLES, CLICK THE RELATED FIELD IN THE FIRST TABLE AND DRAG IT TO THE RELATED FIELD IN THE SECOND TABLE. REPEAT AS NECESSARY TO CONNECT ALL THE TABLES.

5. DOUBLE-CLICK EACH FIELD YOU WANT TO INCLUDE IN THE QUERY FROM THE FIELD LIST.

   OR...

   DRAG THE FIELD FROM THE FIELD LIST ONTO THE DESIGN GRID.

6. IN THE DESIGN GRID, ENTER ANY DESIRED SEARCH CRITERIA FOR THE FIELD IN THE CRITERIA ROW.

7. CLICK THE SORT BOX LIST ARROW FOR THE FIELD AND SELECT A SORT ORDER.

8. CLOSE THE QUERY WINDOW.

9. CLICK YES TO SAVE THE QUERY, ENTER A QUERY NAME, AND THEN CLICK OK.