Page 4 of 13PREVNEXT

Queries VII: Query multiple sources for data

The differences between a relationship and a join

Relationships and joins: Different sets of rules.

At first glance, table relationships and joins look the same. For example, if the tables in your record source have a one-to-many relationship, then the join line shows that. What's more, joins and relationships both have "sides." For example, you can have tables on the "one" and "many" sides of a relationship, while joins have record sources on their "left" and "right" sides.

But remember that relationships and joins aren't the same thing. A relationship is a set of rules that enforces referential integrity — how you can add and delete data. For example, if you delete a supplier, referential integrity controls whether Access removes the products related to that supplier.

In contrast, a join describes how your data must match in order to be included in a record set. For example, one type of join returns data from a source only when it finds matching records in another, and we'll show you what that means in the next section.

You only use joins in queries, and Access lets you use joins in ways that you can't use relationships. For example, you can delete joins without harming your database, but deleting a relationship will break part or all of that database. What's more, you can add joins where no table relationships exist. For example, you can join a table with a query, provided each has a field with a compatible data type.

Keep going to learn more about the kinds of joins you can use in a multi-source query.

Page 4 of 13PREVNEXT