Introducing Joins on Tables

Your purpose in acquiring a license for Access is undoubtedly to take advantage of its relational database management capabilities. To do so, you must be able to link related tables based on key fields that have values in common a process called joining tables. Chapter 9, "Designing Queries for Jet Databases," and Chapter 10, "Understanding Jet Operators and Expressions," showed you how to create simple queries based on a single table. If you tried the examples in Chapter 10, you generated a multiple-table query when you joined the Order Details table to the Orders table and the Customers table to create the query for testing expressions. The first part of this chapter deals exclusively with queries created from multiple tables that are related through joins.

This chapter provides examples of queries that use each of the four basic types of joins that you can create in Access's Query Design view: inner joins, outer joins, self-joins, and theta joins. It also shows you how to take advantage of UNION queries that you can't create in Access's Query Design. The chapter also briefly covers subqueries, which you can substitute for nested Jet queries. Chapter 13, "Creating and Updating Jet Tables with Action Queries," presents typical applications for and examples of four types of action queries: update, append, delete, and make-table.

Some of the sample queries in this chapter use the HRActions table that you created in Chapter 5, "Working with Jet Databases and Tables." If you didn't create the HRActions table and have installed the sample databases from the accompanying CD-ROM, choose File, Get External Data, Import, and import the HRActions table from \Program Files\Seua11\Chaptr05\Nwind05.mdb or ...\Chaptr11\Joins11.mdb, which includes all the examples of this chapter.

For a detailed description of the HRActions table, see "Designing the HRActions Table," p. 179.

Tip

Read this chapter and create the sample queries sequentially, as the queries appear in text. The sample queries of this chapter build on queries that you create in earlier sections.

If you're upgrading from Access 97 to 11, the following features added by Access 2000 apply to the subject matter of this chapter:

• New Query Properties Subdatasheet Name, Link Child Fields, Link Master Fields, Subdatasheet Height, and Subdatasheet Expanded control the behavior of subdatasheets in query result sets.

• You can print the contents of the Relationships window. When the Relationships window has the focus, choosing File, Print Relationships creates a report from the contents of the Relationships window and then displays the report in Print Preview mode for subsequent printing.

Access 2002 introduced extended properties of SQL Server 2000 tables, which let you specify lookup field and subdatasheet properties in Access Data Projects. Extended properties establish parity between Datasheet views of Jet and SQL Server tables and queries.

For a brief list of these extended properties, see "Access Data Projects and SQL Server 2000," p. 34