In the last three chapters, you learned how to create tables, modify them, and link or import tables from other data sources. Although you can certainly build forms and reports that get their data directly from your tables, most of the time you will want to sort or filter your data or display data from more than one table. For these tasks, you need queries.
When you define and run a select query, which selects information from the tables and queries in your database, Microsoft Office Access 2007 creates a recordset of the selected data. In most cases, you can work with a recordset in the same way that you work with a table: You can browse through it, select information from it, print it, and even update the data in it. But unlike a real table, a recordset doesn’t actually exist in your database. Office Access 2007 creates a recordset from the data in the source tables of your query at the time you run the query. Action queries-which insert, update, or delete data-will be covered in Chapter 9, “Modifying Data with Action Queries.”
As you learn to design forms and reports later in this book, you’ll find that queries are the best way to focus on the specific data you need for the task at hand. You’ll also find that queries are useful for providing choices for combo and list boxes, which make entering data in your database much easier.
The examples in this chapter are based on the tables and data from the Conrad Systems Contacts sample database (Contacts.accdb), a backup copy of the data for the Contacts sample database (ContactsDataCopy.accdb), the Housing Reservations database (Housing.accdb), and the backup copy of the data for the Housing Reservations sample database (HousingDataCopy.accdb) on the companion CD included with this book. The query results you see from the sample queries you build in this chapter might not exactly match what you see in this book if you have reloaded the sample data using zfrmLoadData in either application or have changed any of the data in the tables.
Access 2007 provides two ways to begin creating a new query:
Click the Query Wizard button in the Other group on the Create tab on the Ribbon. A dialog box appears that lets you select one of the four query wizards. (You’ll learn about query wizards in Chapter 8, “Building Complex Queries.”)
Click the Query Design button in the Other group on the Create tab on the Ribbon to begin creating new query using the query designer.
To open an existing query in Design view, make sure you have queries showing in the Navigation Pane. To display all the queries in your database, click the bar at the top of the Navigation Pane and click Object Type under Navigate To Category and then click Queries under Filter By Group. You can open the query you want in Design view by selecting the query in the Navigation Pane and then pressing Ctrl+Enter. You can also right-click a query name in the Navigation Pane and click Design View on the shortcut menu. Figure 7–1 shows the list of queries for the Conrad Systems Contacts database. Please note that the figure shows you only some of the queries in the database. Use the scroll bar in the Navigation Pane to see the complete list of queries available in the Conrad Systems Contacts database.
Figure 7–1: The Navigation Pane has been filtered to show all the queries in the Conrad Systems Contacts database.
Figure 7–2 shows a query that has been opened in Design view. The upper part of the Query window contains field lists, and the lower part contains the design grid.
Figure 7–2: A query open in Design view shows the tables and field lists.