Example: Crosstab Query - Count with row totals

To distribute camp t-shirts on the first day of a summer day camp you need a stack of t-shirts for each unit How many of each size do you need for each unit of campers? In this example, (based on a real day camp) there are 12 units, 4 sizes for Adult-sized t-shirts and 4 sizes for Child-sized t-shirts. This could be messy! A crosstab datasheet is a very useful way to display this information.

Rows: The unit and t-shirt type (Adult or Child sizing) combinations are listed down the left (row headings). Each unit needs 2 rows,  one for the Adult sizes and one for Child sizes. A third row appears if a record does not list a t-shirt size.

Columns: The sizes are across the top (column headings). The column <> is for records that do not have a value for the t-shirt size. (Whoops!) This crosstab query also includes a Total for each row, Total Of ID. Very useful when counting out the stacks.

The sizes across the top are in alphabetical order. To sort in order by increasing or decreasing size we would need another table that ordered these sizes and set up a relationship. I was the only one using the database, so I did not bother. I figured I could remember what letter went with what size even though they were out of order.

Cells: The value in a cell is the Count of t-shirts needed of that size (column) for that t-shirt type (row) and camp unit (row).

Examine the 3 rows for the unit J1. This unit needs both Adult size t-shirts and Child sizes. One record in J1 does not have a t-shirt size entered (<>).

A total of 8 adult (A) shirts (Total Of ID):     2 medium (M)    3 small (S)    3 extra large (X)

A total of 8 child (C) t-shirts (Total Of ID):    5 large (L)    2 medium (M)    1 extra large (X).

---

Crosstab Query Wizard

Access tries to make creating a crosstab query easy. The wizard walks you through the choices that you must make. You must pick one or more fields for row headings and one field for column headings. You must pick a field to use for the value in the cell, which can be a calculation like a SUM or COUNT or it can be just the actual value.

Let's look at the steps to create the T-shirts_Crosstab query shown above.

First start the Query Wizard by firstfrom the Create ribbon tab.

The New Query dialog opens and you must click on Crosstab Query Wizard and then on OK.
 

Design View of the query:

The design view of the query shows that it is a Totals query, with a new row in the grid, Crosstab. The choices for this row are Row Heading, Column Heading, Value, and (not shown). Value can be assigned to only one field.