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.
Whether you have an intermediate table in your database or just plan to use one in your query, follow these steps:
Add the intermediate table to your query between the two unconnected tables.
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.
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.
Sometimes you need to query tables or other data sources that are not related, and that don't share a join. You have two options for doing that.
You can use an intermediate table: that is, a table that shares joins with the data sources you want to use.
Or, you can add a join.
Let’s start with the intermediate table. In this example database, employees have to enter a status for each order, but sometimes they forget.
The question you want to answer then is: “Which orders don't have a status, and which employees forgot to enter them?”
On the surface, it looks like we need to use three tables in the query: Employees, Orders, and Order Detail Status because they correspond to the data points in our questions: “Which orders don't have a status, and which employees forgot to enter them?”
Add the tables to the query, and you can see these two tables are not joined, but that's okay, right? We can add a join.
So, we join on the ID fields, run the query, and, ... nothing.
So now, we do what we should have done in the first place, and look at the relationships between the tables.
When we do, we see what looks like a chain of relationships: Employees-to-Orders, Orders-to-OrderDetails, and OrderDetails-to-OrderDetailsStatus.
Orders and OrderDetailStatus are not related, but Order Details is related to them both. That makes OrderDetails our intermediate table.
In turn, that leads to a rule: As long as the intermediate tables are part of your data source, you can query these unrelated tables and get a valid result.
You don't have to include the intermediate table in your results, but it has to be part of your query's data source.
So, we add OrderDetails to the query, … keep the fields the same, … and run the query.
We filter for blank values, … and here's our answer.
Now, let's add a join to a query. To do this, keep some more rules in mind.
First, the tables or queries you want to join need to have fields with matching or compatible data types.
For example, the Number and Currency data types are compatible, but Number and Text are not.
Second, the join you create only resides in your query. You're not creating a table relationship here.
And third, the results should make sense. If the join returns data on orders and company cars, for example, you should probably start over.
Let’s say we need to know which customers ordered which products.
We add Customers, Orders, and OrderDetails to a new query. Now, we add the join. We drag customer ID from the Customers table to the matching field in the Orders table.
Here's the join line and now we have another rule: Access places the field you drag on the left side of the new join. If this isn't what you want, select the join line, press the Delete key, and start over.
Now that we've taken care of that, we can build the query. We add FirstName and LastName from Customers, then Product ID, … Order ID, … Quantity, … and UnitPrice from OrderDetails.
Run the query and here's our data.
Now you know two techniques you can use when you want to query unrelated data sources: use an intermediate table or add a join, and the answers to your data questions appear.