Page 3 of 13PREVNEXT

Queries VII: Query multiple sources for data

Access uses relationships or compatible fields in joins

Most of the time, they're automatic.

To create effective multi-source queries, you need to know your database. Familiarize yourself with the relationships among your tables, and the fields and data types in each table. This course assumes you know how.

Next, you need to follow a couple rules for using joins.

Callout 1 Access always uses your table relationships as a pattern when creating joins in a query. For example, look at the picture. You can see what looks like a relationship between the Products and OrderDetails tables — a line with the "one" and "many" symbols. Because those tables are related, Access creates a similar join in the query. If you've designed your database properly, your table relationships will serve as the basis for the joins in most of your multi-source queries. But what if you need to query sources that aren't related? You use…
Callout 2 The second rule: You can join unrelated sources by using fields with matching or compatible data types. For example, in the picture, the arrows point to a pair of fields with the same name — ProductID. You can join the two tables by using the ProductID fields, and you'll see how to do that later.

What's more, Access will create joins for you, but only when it finds fields with the same name. Those fields must also have matching or compatible data types, and one of the fields must be a primary key.

Next, let's look at the differences between relationships and joins.

Page 3 of 13PREVNEXT