To work with Access queries effectively, it's helpful to distinguish between relationships and joins. It's not a concept that's easily grasped. First I quickly define both terms; then I talk about the difference in a touchy-feely sort of way, and then I tell you "what you need to know."
As you've seen, a relationship is an association established between common fields in two tables. A join is also an association between a field in one table and a field in another table, but within the context of a specific query. The join makes it possible to combine data from different tables and execute a multitable query.
When you establish a relationship in Access between two tables, you've essentially sold them an engagement ring. When you enforce referential integrity, however, you've pronounced them man and wife. Each table is entering an extended family with which it establishes strong bonds. As you've seen with cascading deletes, an action taken in one table can ripple through the entire database with enormous consequences. In a well-integrated database, relationships are not easily severed.
Let's say it's Saturday night. If you're married, it can be assumed you'll be spending it with your spouse. That's the way Access considers relationships when it comes to multitable queries. When you earlier added the Suppliers and Merchandise tables to the query, Access didn't even bother to ask whether these two tables wanted to be joined together. Because they were in a relationship, it was just assumed that for purposes of the queryfor that Saturday nightthey would want to be together.
And Access is nearly always right. If you're creating a multitable query, the relationships you've already established will create joins between the tables for that particular query.
But although Access automatically creates join lines for tables that have a relationship, two tables don't need to have a relationship to be joined. They could simply meet up for purposes of the querythat one Saturday night dateand depart.
Consider the example you did using the Suppliers and Merchandise tables. If the two tables didn't have a relationship, you could have simply dragged the SupplierID field from one field list to the other to create a join for that particular query. In fact, if the two fields have matching data and one of the join fields is a primary key, Access takes the initiative and creates the join for you (assuming that Enable AutoJoin is implemented, as it is by default; the option is on the Tables/Queries tab of the Options dialog box, available from the Tools menu). Although this join can be saved for a particular query, it does not establish a relationship between the tables.
What You Need to Know
My explanation may or may not have been helpful. But you'll be okay if you remember that:
Creating a Join Manually
Let's look at an example in which no relationship exists between two tables but you can use a join to create a query and find the data you need. The example might not be your most likely information requirement, but it's not completely unrealistic, either.
Suppose you want to determine whether there are any customers and suppliers who are in the same state. In Figure 8.10, I have hidden and tiled several columns in the Customers and Suppliers tables. If you compare the two tables carefully, you'll notice that only one customer and supplier are in the same state: Customer Halliburton and supplier Lyons and Taigras are both in Illinois.
Figure 8.10. A single customer and supplier are in the same state.
The two tables do not have a relationship, but their state fields have matching data. Instead of scrutinizing table lists, let's create a query and use a join between the two state fields to find this record quickly.