Relationships Versus Joins

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."

Definitions

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.

Touchy-Feely Explanation

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:

• If you've crafted a database that is completely integrated through relationships, you'll rarely need to create joins on your own. Access will automatically create the joins you need to perform multitable queries.

• If you do not want to establish a relationship between tables, you can create a join between them for purposes of the query if there are fields in each table with matching data. Simply drag the field with matching data from one field list to the other.

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.

1.	In the Database window, click Queries and click New. With Design View chosen in the New Query dialog box, click OK.
2.	Add tblCustomers and tblSuppliers to the design grid. There is no relationship between these tables, and no join has been established.
3.	Save the query as qrySuppliersCustomers_SameState.
4.	In the tblCustomers field list, click CustState and drag and drop it on SuppState in the tblSuppliers field list. You see a line between the two fields, which shows you've created a join.
5.	Double-click CustFirstName and CustLastName from the Customers field list to add them to the grid.
6.	Double-click SuppCompanyName and SuppState from the Suppliers field list to add them to the grid (see Figure 8.11). Figure 8.11. In this query, a join could be created manually because the two fields have matching data.
7.	Click View to see the one record with matching data.
8.	Save your changes and close the query.