Related topics
×
Manage data with queries
Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
Manage data with queries

Query unrelated data sources

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

Try it!

Sometimes you need to create a query to pull information from data sources that don’t have a direct (one-to-one or one-to-many) relationship.

Use intermediate tables to connect data sources in queries

When you run a query, Access uses the relationships you’ve created between tables and it deduces and creates connections between other data sources. If the relationship you need for a query doesn’t exist, connect the tables as part of that query.

Suppose you want to see how many orders you’ve received for products from different suppliers. Your Suppliers table isn’t connected to your Purchase Order Details table, which contains the product IDs and quantity information. However, the Purchase Orders table is connected to the Purchase Orders Details table.

Multiple table data sources, with and without predefined relationships.

Tip: If you might need this relationship outside the query you’re creating, it’s best to create an intermediate relationship for later use. To learn how, see Create many-to-many relationships.

If you don’t want to create the intermediate table in your database, use the Purchase Orders table as the intermediate table in your query. Add the Suppliers table to the left of the Purchase Orders table, and create a connection.

Using one table to indirectly connect two other tables

The Purchase Orders table connects the two disparate tables, Suppliers and Purchase Order Details. Connect the tables using fields (such as ID in the Supplier table, and Supplier ID in the Purchase Orders table) that meet the following criteria:

  • The two fields have matching or compatible data types. For example, you can’t connect a text field to a number field.

  • The fields identify matching, unique records in each table. For example, you wouldn’t want to connect two Last Name fields because last names aren’t always unique.

  • The fields ensure that the correct records result. For example, if you connect Supplier ID to Purchase ID, you might get some matches if the IDs are similar. But the results won’t make sense because the Supplier ID has nothing do with the Purchase ID. A better choice is to connect ID from the Supplier table to Supplier ID in the Purchase Orders table—the records returned will make sense because both fields identify the supplier.

Steps

Whether you have an intermediate table in your database or just plan to use one in your query, follow these steps:

  1. Add the intermediate table to your query between the two unconnected tables.

  2. Create any needed connections with the intermediate table.

    Connections can use any appropriate type of join but must be connected through fields that meet the criteria described earlier in this module.

    Creating needed connections with the intermediate table
  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.

    Note: You don’t have to include the intermediate table in your results. It just needs to be part of the query’s data sources so that Access can connect the data.

Want more?

Create basic queries

Create queries by joining more than one data source

Create queries with outer joins

Excel training

Outlook training

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×