Chapter 8: Building Complex Queries

Overview

Creating queries on a single table as you did in the previous chapter is a good way to get acquainted with the basic mechanics of the query designer. It’s also useful to work with simple queries to understand how datasheets work.

However, for many tasks you’ll need to build a query on multiple tables or queries (yes, you can build a query on a query!), calculate totals, add parameters, customize query properties, or work in SQL view. In fact, there are some types of queries that you can build only in SQL view. This chapter shows you how.

Caution 

When you build an application, you should never allow users to view or edit data directly from table or query datasheets. Although you can protect the integrity of your data somewhat with input masks, validation rules, and relationships, you cannot enforce complex business rules.

For example, in the Housing Reservations application, you need to ensure that a particular room isn’t booked more than once for a given time period. In the Conrad Systems Contacts application, your application shouldn’t allow a support contract to be sold for a product that the contact hasn’t purchased. You can’t run such integrity checks from tables or queries. However, you can enforce such complex business rules by writing Visual Basic code in the forms you design so that your users can edit data while maintaining data integrity.

The purpose of this chapter is to teach you the concepts you must learn to build the queries you’ll need for your forms and reports. In Chapter 20, “Automating Your Application with Visual Basic,” you’ll learn how to build complex business rule validation into your forms.