Create queries with more than one data source

Try it!

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.

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.

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.

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