Share this Page URL

Chapter 9. Extending Your Use of Queries > Summarizing Data with a Crosstab Que... - Pg. 305

Extending Your Use of Queries 305 Figure 9-1. With a crosstab query, you use field values as the headings for columns and rows and summarize related values Queries we've added to the database The version of the HelloWorld database we use in this chapter includes several select queries that we'll use as the basis for the action queries we create and in other examples. If you need information about how to create simple select queries, see Chapter 6. When you create a new query, you can select Crosstab Query Wizard in the New Query dialog box. With the wizard, you can build a crosstab query using fields from a single table or base the query on a select query (which could include fields from more than one table). To create a crosstab query that uses fields from more than one table (assuming you haven't created a select query for this purpose), you use the query design grid to specify the tables and fields and then choose Crosstab from the Query menu. Crosstab queries and PivotTables A crosstab query resembles a PivotTable, which you might have used in Microsoft Excel. Access 2003 also supports PivotTables, which provide a way to view summary data from a number of perspectives by "pivoting" the data. In a PivotTable, you can see detailed data as well as summary data, and you can include more than one column heading. We'll describe the use of PivotTables in Access 2003 in more detail in Chapter 11 , "Analyzing Data with PivotTables and PivotCharts." In the following steps, we'll create a crosstab query that provides a view of the products that are ordered in the various countries where customers are located. We'll use the query design grid in this example. After following these steps, you'll find it easy to use the Crosstab Query Wizard if you prefer. Create a crosstab query 1. 2. 3. 4. Open the HelloWorld9.mdb file, located in the Chap09 folder where you copied the sample files for this book. In the Database window, click Queries, and then click New. In the New Query dialog box, select Design View, and then click OK. In the Show Table dialog box, hold down the Ctrl key and select the Customers table, the Order Details table, the Orders table, and the Products table. Click Add, and then click Close. The query design grid now displays the four tables and shows the relationships between them. You can move the field lists so that the relationships between the tables are clearer, as shown here: