In This Site

In This Section

The number one source for making your website sell!

In our last tutorial, Getting Started with Queries, you learned the basics of creating a query in Microsoft Access. In this lesson, we'll take this one step further and show you how to perform query operations with more than one table at a time.

This is where the real power of Microsoft Access queries begins to show. You can add as many tables or queries as you need to create extremely simple OR extremely complex query operations.

In this example, we'll create a query that will show a list of suppliers AND the products they provide. In the Northwind Database, the supplier contact information is kept in one table, but the list of products is kept in a separate table. This is an example of good database design.

In order for the two tables to be able to relate to one another, each must have one field that is common between them. In the Nortwind Supplier table, each supplier is assigned a SupplierID. This is an autonumber field that provides a unique identifier for each supplier. When new products are added, each new product record will also have a SupplierID field. This will connect the product to the relevent supplier.

This connecting of tables is done during the database design phase. In Access, if you want to view or edit the relationships between tables, select Tools-Relationships. In the case of these two tables, they have a one-to-many relationship. This means that for each supplier, there may be many product records.

Let's get started. Open the Northwind database and click on the query pane. Select New and then Design View.

In the blank query window, add the Supplier table and then the Products table. You will see a line joining the SupplierID field from the Supplier table to the Products table. Your screen will resemble the following:

Now, to get a list of suppliers and the products they sell, we'll drag some fields from each of the tables onto the query grid below.

From the Suppliers table, drag the CompanyName field into the first field of the Query Design grid.

From the Products table, drag the ProductName and UnitsInStock fields to the grid.

Your screen will look like the following:

Now, in the Sort field of the CompanyName and ProductName field select the Ascending option. This will sort by Company Name and then by Product Name. Click the preview button and your data will resemble the following:

That's all there is to it. You've now created a query that combines values from two different tables. Save this query as qQueryTwoTables. You can now access this query in FrontPage just as easily as you would any of your tables. This is the very best way of creating queries that you want to access online because it provides a great deal of flexibility and requires little or no knowledge of SQL programming language.

In our next tutorial, you'll learn how to query multiple tables at the same time. Click here to read it now