A crosstab query lets you combine two or more fields to calculate and display a calculation based on a third field. For example, if your database contains the names of salespeople and the products they sold, you can use those two fields to create a crosstab that tells you how much each salesperson sold of each product, as shown in Figure 17-12.
A crosstab query extracts information by cross-referencing two or more fields.
To create a crosstab query, you need to identify three types of fields:
One to three fields to identify each record (such as the First Name and Last Name fields)
A single field to display specific data from each record (such as the Product field, which displays the actual product names like Nails, Barbed Wire, and Screws)
A crosstab field that displays a calculated result (such as Sales)
To create a crosstab query, follow these steps:
1. Click the Create tab.
2. Click the Query Wizard icon in the Other group.
The New Query dialog box appears (refer to Figure 17-8).
3. Click Crosstab Query Wizard and then click OK.
The Crosstab Query Wizard dialog box appears, as shown in Figure 17-13.
The Crosstab Query Wizard dialog box asks you to choose which database table to use.
4. Click a database table and then click Next.
Another Crosstab Query Wizard dialog box appears that asks for between one and three fields to identify each row (record), as shown in Figure 17-14.
5. Click a field in the Available Fields box and then click the > button to move your chosen field to the Selected Fields box.
6. Repeat Step 5 for each additional field you want to include.
The first step to creating a crosstab query is to choose up to three fields to identify each record.
7. Click Next.
Another dialog box appears, asking for a single field to use to cross-tabulate data with the fields you chose in Steps 5 and 6, as shown in Figure 17-15.
To cross-tabulate your data, you need to choose another field.
8. Click a field name and then click Next.
Ideally, this field should consist of text information that lists different data, such as sales regions (East, West, North, or South) or products (Hammers, Screws, Nails, and so on). If you choose a field that contains numerical data, your crosstab query displays only those numbers in the column headings, which will appear meaningless. Another dialog box appears, as shown in Figure 17-16.
Choose must contain numerical data for the crosstab query to calculate.
9. Click a field that contains numerical data and then click a mathematical function that you want Access to calculate, such as Sum, Avg, or Count.
10. Click Next.
Another dialog box appears, asking for a name for your query, as shown in Figure 17-17.
After you create a query, give it a descriptive name.
11. Type a descriptive name for your query in the text box at the top of the dialog box and then click Finish.
Access displays your crosstab query (refer to Figure 17-12).
12. Click the Office Button and choose Save to save your query.