Microsoft Office Tutorials and References
In Depth Information
Chapter 4: Viewing Your Data from All Angles Using Crosstabs
Chapter 4: Viewing Your Data from
All Angles Using Crosstabs
In This Chapter
Understanding Crosstab queries
Running the Crosstab Query Wizard
Creating Crosstab queries in Design view
Sometimes, instead of viewing your data in records, you want to see it
organized and categorized. (What a concept.) You may want to see
sales of each product by month, and you may want to see that information
in a compact table, with months as the column titles, product names as the
row titles, and the sum of sales in the body of the table. Access creates that
kind of table with a Crosstab query. Crosstab queries organize data and
create totals by using the aggregate function of your choice, Sum, Avg (aver-
age), and Count being the most popular. You create Crosstab queries in
Design view.
Aggregating Data in a Crosstab Query
A Crosstab query is a specialized query for summarizing data. Instead of
creating a table with rows showing record data and columns showing fields,
you can choose a field and group its data by using two other fields as row
and column labels. Access groups the data the way you tell it and
aggregates the grouped field in the body of the table. You can choose among the
usual aggregate functions, such as Sum, Avg, Min (minimum), Max (maxi-
mum), and Count.
It’s far easier to show you than to explain. Figure 4-1 uses the ProductName
field for the row labels, the Order Date Month field for the column labels,
and the field that contains the sales subtotal for the product (price ×
quantity) as the information to put in the body of the table, and tells Access to
sum the result. The resulting Crosstab query is shown in Figure 4-1, with
sales of each product displayed by quarter. (You can choose the time
period, too.) The result is a compact, spreadsheetlike presentation of
your data.
Search JabSto ::




Custom Search