Querying Multiple Tables

In This Site

In This Section

The number one source for making your website sell!

Using Microsoft Access to Query Multiple Tables

In our last lesson, you learned how to query two tables in Access 2002. In this lesson, you'll take that one step further and create a complex query using four tables.

On a blank Query design window, drag the following tables to the query design window: Employees, Orders, Order Details and Products. As in the last lesson, you will see relationship lines joining individual records in several of the tables.

From the Employees table, drag the EmployeeID, LastName and FirstName fields to the Query window. From the Orders table, drag the OrderID and OrderDate field. Finally, drag the ProductName from the Products table and the Quantity from the Order Details table. Your query design window should look like the following:

Now, click on the preview window. Your data will resemble the following:

Return to the query design window and click the sort button under LastName and select Ascending. Now, do the same for the FirstName field. In the OrderDate field, select sort and choose Descending. This will sort your table in alphabetical order and then place the orders with the most recent showing first. Your table will now look like this:

In this new query grid, you now have a list of Employees along with the details of all of their sales.

The key thing that makes this work is the fact that at least one field is common between each group of two tables.

The EmployeeID is common between the Employees and the Orders tables
The OrderID field then connects the Orders with the OrderDetails table
Finally, the ProductID connects the OrderDetails and Products tables

This cascading group of connections is necessary to match the data between each employee and the products that were ordered. You'll notice that there is NO connection directly between the Employees and the Products tables. You cannot pull up a list of products sold by a particular employee unless you add a table that has a field to connect the two. In this case, you would need all four tables to create the connection between these two tables.

In our next lesson, you'll learn how to limit which records are displayed by using criteria in your queries. Click here to read it now

These tutorials are part of an upcoming training course called "FrontPage Magic - How To Create A Database Driven Website For Non-Programmers". Stay tuned for more details on this exciting new product.