• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Part 2: Designing Queries > Creating Multitable and Crosstab Queries

Chapter 10. Creating Multitable and Crosstab Queries

Your purpose in acquiring Access is undoubtedly to take advantage of this application's relational database management capabilities. To do so, you have to be able to link related tables based on key fields that have values in common—a process known as a join in database terms. Chapters 8, "Using Query by Example," and 9, "Understanding Operators and Expressions in Access," showed you how to create simple queries based on a single table. If you tried the examples in Chapter 9, you saw a glimpse of a multiple-table query when you joined the Order Details table to the Orders table, which you then joined to 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: equi-joins, outer joins, self-joins, and theta joins. Two of the three new query features introduced by Access 2.0, subqueries and UNION queries, cannot be used in the queries that you design in Access's graphic Query by Example (QBE) window. You can implement these two new query features only by writing SQL statements—the subject of Chapter 23, "Working with Structured Query Language." Some of the example queries in this chapter use the Personnel Actions table that you created in Chapter 4, "Working with Access Databases and Tables." If you didn't create the Personnel Actions table, refer to the Creating the Personnel Actions Table section of Chapter 4 or to Appendix C, "Data Dictionary for the Personnel Actions Table," for instructions on how to build this table.

Other example queries build on queries that you create in preceding sections. You will find, therefore, that reading this chapter and creating the example queries sequentially, as the queries appear in text, is more efficient than taking the random approach.

This chapter also includes descriptions and examples of four of the five categories of queries that you can create with Access: select, summary, parameter, and crosstab queries. Four types of action queries exist that you can use to create or modify data in tables: Make-table, Append, Delete, and Update. The next chapter, "Using Action Queries," presents typical applications for and examples of each type of action query.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint