Video query unrelated data sources

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.

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.

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.

   

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