Access 2016: Designing a Multi-table Query
www.office.com/setup Blogs: Introduction
www.office.com/setup Blogs: In the previous lesson, you learned how to create a simple query with one table. Most queries you design in Access will likely use multiple tables, allowing you to answer more complex questions. In this lesson, you'll learn how to design and create a multi-table query.Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2016 sample database. You will need to have Access 2016 installed on your computer in order to open the example.
Watch the video below to learn how to create a multi-table query (Part 1).
Watch the video below to learn more about joins and query criteria (Part 2).
Designing a multi-table query
Queries can be difficult to understand and build if you don't have a good idea of what you're trying to find and how to find it. A one-table query can be simple enough to make up as you go along, but to build anything more powerful you'll need to plan the query in advance.Planning a query
When planning a query that uses more than one table, you should go through these four steps:- Pinpoint exactly what you want to know. If you could ask your database any question, what would it be? Building a query is more complicated than just asking a question, but knowing precisely what question you want to answer is essential to building a useful query.
- Identify every type of information you want included in your query results. Which fields contain this information?
- Locate the fields you want to include in your query. Which tables are they contained in?
- Determine the criteria the information in each field needs to meet. Think about the question you asked in the first step. Which fields do you need to search for specific information? What information are you looking for? How will you search for it?
Planning our query
Let's go through this planning process with a query we'll run on our bakery database. As you read through the planning process step by step, think about how each part of the planning process could apply to other queries you might run.Step 1: Pinpointing the question we want to ask
Our bakery database contains many customers, some of whom have never placed an order but who are in our database because they signed up for our mailing list. Most of them live within the city limits, but others live out of town or even out of state. We want to get our out-of-town customers who've placed orders in the past to come back and give us another try, so we're going to mail them some coupons. We don't actually want our list to include customers who live too far away; sending a coupon to someone who doesn't live in our area probably won't make that person come in. So we just want to find people who don't live in our city but who still live in our area.![Identifying the data we want the query to find - www.office.com/setup](https://cdn-ak.f.st-hatena.com/images/fotolife/o/octaonenetwork/20180715/20180715072327.png)
Step 2: Identifying the information we need
What information might we want to see in a list about these customers? Obviously, we'll need the customers' names and their contact information—their addresses, phone numbers, and email addresses. But how are we going to know if they've placed orders? Each record of an order identifies the customer who placed that order. If we include the order ID numbers, we should be able to narrow our list down to only customers who have previously placed orders.![Making a list of the information we want the query to find - www.office.com/setup](https://cdn-ak.f.st-hatena.com/images/fotolife/o/octaonenetwork/20180715/20180715072334.png)
Step 3: Locating the tables containing the information we need
In order to write a query, you need to be familiar with the different tables in your database. From working extensively with our own database, we know that the customer information we need is located in fields in the Customers table. Our Order ID numbers are in a field in the Orders table. We only need to include these two tables to find all of the information we need.![Making a list of the tables where we can find the information we need for our query - www.office.com/setup](https://cdn-ak.f.st-hatena.com/images/fotolife/o/octaonenetwork/20180715/20180715072341.png)
Step 4: Determining the criteria our query should search for
When you set criteria for a field in a query, you are basically applying a filter to it that tells the query to retrieve only information that matches your criteria. Review the list of fields we are including in this query. How and where can we set criteria that will best help us answer our question? We don't want customers who live in our town, Raleigh, so we want a criteria that will return all records except for those with Raleigh in the city field. We don't want customers who live too far away, either. All of the phone numbers in the area start with the 919 area code, so we'll also include a criteria that will only return records whose entries from the phone number field begin with 919. This should guarantee that we'll only send coupons to customers who live close enough to actually come back and use them. We won't set a criteria for the order ID field or any other fields because we want to see all of the orders made by people who meet the two criteria we just set.![Figuring out the criteria we will use to build our query - www.office.com/setup](https://cdn-ak.f.st-hatena.com/images/fotolife/o/octaonenetwork/20180715/20180715072348.png)
To write queries, you'll need to be able to set criteria in a language that Access understands. As you can see in the image above, our criteria requiring phone numbers to begin with 919 must be typed like this: Like ("919*"). To learn how to write additional criteria, consult our printable Query Criteria Quick Reference Guide, which includes several of the most common criteria used in Access queries.
Joining tables in queries
The final thing you need to consider when designing a query is the way you link—or join—the tables you're working with. When you add two tables to an Access query, this is what you'll see in the Object Relationship pane:![Joined tables in the Object Relationship Pane - www.office.com/setup](https://cdn-ak.f.st-hatena.com/images/fotolife/o/octaonenetwork/20180715/20180715072320.png)
![Related data stored in the Customers and Orders tables - www.office.com/setup](https://cdn-ak.f.st-hatena.com/images/fotolife/o/octaonenetwork/20180715/20180715072248.png)
![The Left to Right join retrieves all the records from the table on the left first. - www.office.com/setup](https://cdn-ak.f.st-hatena.com/images/fotolife/o/octaonenetwork/20180715/20180715072255.png)
![The query then retrieves the orders linked to the customer records it already pulled. - www.office.com/setup](https://cdn-ak.f.st-hatena.com/images/fotolife/o/octaonenetwork/20180715/20180715072301.png)
![The Right to Left join retrieves all the records from the table on the right first. - www.office.com/setup](https://cdn-ak.f.st-hatena.com/images/fotolife/o/octaonenetwork/20180715/20180715072307.png)
![Next, Access retrieves only the records from the left table that are linked to existing orders from the right. - www.office.com/setup](https://cdn-ak.f.st-hatena.com/images/fotolife/o/octaonenetwork/20180715/20180715072314.png)
In our query, we needed to use the right-to-left join, but the correct join direction for the tables in your queries will depend on what information you want to see and where that information is stored. When you add tables to a query, Access will automatically join the tables for you, but it often doesn't join them in the correct direction. This is why it's important to always review the joins between your tables before you build a query.
Creating a multi-table query
Now that we've planned our query, we're ready to design and run it. If you have created written plans for your query, be sure to reference them often throughout the query design process.To create a multi-table query:
-
- Select the Query Design command from the Create tab on the Ribbon.
- In the dialog box that appears, select each table you want to include in your query and click Add. You can press and hold the Ctrl key on your keyboard to select more than one table. When we planned our query, we decided we needed information from the Customers and Orders tables, so we'll add these.
- After you have added all of the tables you want, click Close.
- The tables will appear in the Object Relationship pane, linked by a join line. Double-click the thin section of the join line between two tables to edit its join direction.
- The Join Properties dialog box will appear. Select an option to choose the direction of your join. In our example, we'll choose option 3 because we want a right-to-left join.
- In the table windows, double-click the field names you want to include in your query. They will be added to the design grid in the bottom part of the screen. In our example, we'll include most of the fields from the Customers table: First Name, Last Name, Street Address, City, State, Zip Code, and Phone Number. We'll also include the ID number from the Orders table.
- Set field criteria by entering the desired criteria in the criteria row of each field. We want to set two criteria: Not in ("Raleigh") in the City field, and Like ("919*") in the Phone Number field. This will find customers who do not live in Raleigh but who do live in the 919 area code.
- Select the Query Design command from the Create tab on the Ribbon.
![Setting field criteria - www.office.com/setup](https://cdn-ak.f.st-hatena.com/images/fotolife/o/octaonenetwork/20180715/20180715072158.png)
- After you have set your criteria, run the query by clicking the Run command on the Design tab.
- The query results will be displayed in the query's Datasheet view, which looks like a table. If you want, save your query by clicking the Save command in the Quick Access Toolbar. When prompted to name it, type the desired name, then click OK.
Challenge!
- Open our practice database.
- Create a new query.
- Select the Customers and Orders tables to include in your query.
- Change the join direction to right to left.
- Add the First Name, Last Name, and Zip Code fields from the Customers table to your query.
- Add the Paid field from the Orders Table to your query.
- Set the following criteria: In the Zip Code field, type 27609 to return only records with a zip code of 27609. In the Paid field, type Yes to return only customers who have paid.
- Run the query. If you entered the query correctly, your results will include 20 records of customers who live in the zip code 27609 and have paid for an order. If not, click the View drop-down arrow on the Ribbon to return to Design view and check your work.
- Save the query with the name Paying Customers in 27609.