Create queries by joining more than one data source

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Often, to get the answers you need, you create queries that gather information from more than one table or from a combination of tables and queries.

Get started with joins

Sometimes you have to use data from more than one source (table or query) to answer a question. For example, you might want to see how many customers in a certain region ordered a specific product.

When you add more than one data source, Access uses the existing, defined relationships between them or it creates joins between them for query purposes. A join is simply a type of relationship between data sources in a query.

Note: Joins are temporary and are only associated with the query. If you frequently create the same join between tables, you might want to create a permanent relationship between them.

Inner joins

Most joins are inner joins. This is opposed to outer joins, which are discussed in Create queries with outer joins.

An inner join returns data from one source only when it finds matching records in the other. For example, if you query for information about products and orders, you only see data for products that have a matching order.

Screenshot of Product and Orders tables

Self-joins

A self-join joins two copies of the same table or query. Use it to combine records from the same table when there are matching values in the joined fields.

For example, suppose you have an Employees table that lists all the employees in your organization, including the managers. The table contains a Reports To field, which holds the ID of the employee's manager.

ID

Last Name

First Name

Reports to

1

Freehafer

Nancy

5

2

Cencini

Andrew

3

Kotas

Jan

10

4

Sergienko

Mariya

5

5

Thorpe

Steven

2

6

Neipper

Michael

10

7

Zare

Robert

10

8

Guissani

Laura

10

9

Hellung-Larsen

Anne

5

10

Dempsey

Molly

3

To find the name of an employee’s manager, find the employee in the table, find the manager ID in the Reports To field, and then look up the manager’s ID in the same Employees field. For example, Nancy reports to the manager whose employee ID is 5—that’s Steven Thorpe.

To automate this task, add the Employee table to your query twice and then create a self-join. When you add the data source a second time, Access appends _1 to the name of the second instance. For example, if you add the Employees table twice, the second instance is named Employees_1. To display the manager names for each employee, you create a join between the Reports To field in the Employees table and the Employee ID field in the Employees_1 table.

1. Add data sources
  • Select Create > Query Design.

Add all the tables and queries you need as data sources. (Don’t worry. If you forget one, you still have an opportunity to add more data sources later.)

The data sources might include tables from which you want to display information (like a products table) and tables containing information you need to use for criteria.

Remember that existing, saved queries are also available as data sources. For example, if you already created a query to find all the orders for a given month, use that query plus the products table to show all the products sold in a given month.

2. Join related data sources
  • To add a join yourself, drag a field from one data source to a corresponding field in another data source. By default, Access creates an inner join.

For most relationships, Access creates an inner join. And, if you’ve specified that referential integrity should be enforced, it’s included. But, in some cases, Access doesn’t create the join that you need. For example, if you add existing queries to your new query and haven’t created relationships, Access doesn’t automatically create joins.

3. Complete the query
  • Follow the usual steps for creating a query: add output fields, add any criteria, and run or save your query. To learn how, see Create basic queries.

Want more?

Excel training

Outlook training

In this video, you’ll learn how to create a Select query with multiple data sources.

You’ll learn how to use “joins” to create a match between the multiple sources.

You’ll also learn about the various types of joins available.

Building a Select query that uses more than one data source follows the same basic steps as building one with a single data source.

You open the Query Designer, select your tables or queries, and add your fields.

But there's a twist.

When you query multiple sources for data, you need to create a match between each source.

For example, if you want to know which products are in the set of orders, you have to match the orders with their products.

To create these matches, you use what's called a "join."

A JOIN compares the data in two common fields: one from each data source.

The join controls how the data in each field is matched.

For example, the most common type of join only returns data from one field when it finds a matching value in the other, and this is how you find out the number of products in each order.

Another type of join returns ALL the data from one field, but only MATCHING data from the other.

And in this example, the query is telling you which orders don't contain a given product.

Now, when you created your table relationships, Access also created a JOIN for each relationship.

Access uses your relationships as PATTERNS for joins, so most of the time your joined fields will be your primary and foreign keys.

But they don't have to be. You can also join unrelated sources.

If you do have two related sources and you add them to the Query Designer, you see what looks like the relationship—just remember that it isn't. Access is only telling you the type of relationship it used as the PATTERN for the join.

And as long as you have a join between your tables or queries, you can add fields from both sources.

Notice too, that we didn't add the joined fields to the design grid. We don't need to, because Access adds them behind the scenes.

You can run the query and get a result.

Now let's take a closer look at joins, because you need to remember a couple of key points.

First, you can set or change the type of join by right-clicking or double-tapping the join line and selecting Join Properties.

These settings control the type of join in your query and these are the most common types of joins.

This option creates an “inner join.” This is the type of join that returns matching data from BOTH tables.

Access creates this type of join whenever you create a relationship.

The second option creates a "left outer join," which means that your query returns ALL the data from the table listed on the left and only MATCHING data from the other table.

The third option creates a "RIGHT outer join" and you can probably guess what happens.

The query returns ALL the data from the table on the right and only MATCHING data from the table on the left.

You typically use outer joins when you want to know what hasn't sold or more generically, when something hasn't happened.

Recall that these are the three most common types of joins. You can also use "cross joins" and "unequal joins" but these aren’t used very often.

The other key point about joins is that "relationships" and "joins" are not the same thing.

A RELATIONSHIP is a set of rules that controls referential integrity: how you add and delete data.

A JOIN controls how your query matches data.

But, the two can be kind of similar.

For example, your relationships can have sides—one and many—and your joins can also have sides—left and right—but you only use joins in queries, and you can use them in ways that you can't in relationships.

For example, you can delete joins without harming your database, something you can’t do with a relationship.

Also, you can add joins where relationships don't exist.

For example, you can join a field in a table with a field in a query as long as those fields have matching or compatible data types.

Now you know how to work with joins to create a select query with multiple data sources. You also can set or change the type of join. And you understand the similarities and differences between joins and relationships.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×