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

Chapter 5. Working with Queries > Creating Crosstab Queries

Creating Crosstab Queries

Crosstab queries are like spreadsheets in that they both summarize data based on three values: a row value, a column value, and data that represents the intersection of a row and column. If, for example, you want to search the books in your library to see how many pages a certain author had written for certain publishers, you can use a Crosstab query.

To create a Crosstab query:

1.
Create a new select query in Design View.

For this example we added the Authors, Book Authors, and Books tables to the query design screen. From there, we selected the LastName field from the Authors table and the PublisherName and Pages fields from the Books table ( Figure 5.27 ).

Figure 5.27. Select fields from various tables.


2.
Choose Query > Crosstab Query ( Figure 5.28 ).

Figure 5.28. The Crosstab Query menu item.


When you select Crosstab Query from the Query menu, a Crosstab row appears in the design grid.

3.
Click the Crosstab cell under the field you want to use as your row heading. Click the drop-down menu button that appears and choose Row Heading ( Figure 5.29 ).

Figure 5.29. Choose your row heading…


4.
Do the same for the field that contains your column headings, but choose Column Heading from the drop-down menu. We used the PublisherName field ( Figure 5.30 ).

Figure 5.30. …and your column heading…


5.
Click the Crosstab cell under the field that contains the data for the body of the query, click the drop-down menu button that appears, and choose Value from the menu ( Figure 5.31 ).

Figure 5.31. …the source of your Values…


6.
You're not quite done with the field that will be providing your values. Click in the Total cell, click the drop-down menu button that appears, and choose Sum ( Figure 5.32 ).

Figure 5.32. …and how you want to summarize your data.


The options in the Total drop-down menu represent the different types of summaries Access can perform on the data in the Value column.

7.
Your Crosstab query will calculate the pages each author has written for each publisher. Click the Run button to run it ( Figure 5.33 ).

Figure 5.33. Click the Run button and you'll be off and running.



PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


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