Share this Page URL

Chapter 6. Sorting, Filtering, and Retri... > Designing Select Queries - Pg. 210

Sorting, Filtering, and Retrieving Data 210 Turning off AutoJoin If you don't want Access to create a relationship between unrelated tables in a query automatically, click Tools, Options, and then click the Tables/Queries tab. Clear the Enable AutoJoin check box. (Relationships created in the Relationships window aren't affected by this option.) Multitable Queries Queries that include more than one table bring related data together in a single recordset. Tables and queries you add to a query inherit any relationships set up in the Relationships window. For tables for which a relationship hasn't been created, Access creates a relationship in a query auto- matically if the tables contain a field with the same name and data type and if one of these fields is a primary key. When, for the purposes of a query, you need to retrieve records from tables that aren't related, you can join the tables in the Query Design window. (To join tables in the Query Design window, use the same steps you use in the Relationships window. In the field list for the primary table, select the field that will join the tables, and then drag it to the matching field in the other table.) You can create a relationship between tables in the query design grid, provided the fields have the same data type and contain the same kind of data. (You would not, for example, want to create a relationship between an ID field and a field that contains sales totals even though both fields have the Number data type.) Entering and Updating Data in a Query If a query includes data from only one table (or from tables in a one-to-one relationship), you can update the table's data in the query. In a multitable query using fields from tables in a one-to-many relationship, you're restricted with respect to what data you can update in the query. You can't update the field that joins two tables