Tables & Queries:
Crosstab Query

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

A crosstab query Button: Crosstab query displays a table of "total" values, grouped on at least two fields, one used as row headings and one as column headings. The "total" is a sum, count, average, or other calculation.

Example: Averages

A datasheet of score averages is a crosstab, like the one below from the LanguageArtsClasses database. Students with their ID numbers are listed down the left (2 sets of row headings) and the subjects are across the top (column headings). The value in the cell is the average of the scores that student (row) made on assignments in that subject/type (column). If there is only 1 score per assignment, the "average" winds up being just the plain score. (How tricky!)

Example: Crosstab query

 Icon: Mouse click Click the image to see labels

A similar crosstab could average the term grade for a subject.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing TablesTo subtopics
    Designing Queries Arrow: subtopic open
    Icon: StepSelect Query
    Icon: StepCalculated Values-Text
    Icon: StepCalculated Values-Numbers
    Icon: StepCalculated Values-Totals
    Icon: StepCalculated Values-Dates
    Icon: StepParameter Query
        Action Queries
    Icon: StepMake-Table Query
    Icon: StepAppend Query
    Icon: StepDelete Query
    Icon: StepUpdate Query
    Icon: StepCrosstab Query
    Icon: StepDocument a Query
    ExercisesTo subtopics

Project 4: Forms & Reports


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 combinations of unit and t-shirt type (Adult or Child sizing) are listed down the left (row headings). Each unit needs at least 2 rows, one for the Adult sizes and one for Child sizes. A third row appears if there is a record that 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 and the units down the side are in alphabetical order. To sort columns in order by increasing or decreasing size we would need another table that ordered these sizes and set up a relationship.

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).

Results of Crosstab query

 Icon: Mouse click Click the image to see labels

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 (Column <>).

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 is a calculation like a SUM or COUNT.

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

Dialog: New Query - Crosstab Query WizardButton: New Object - QueryYou start the Crosstab Query Wizard from the New Objects button:
   New Objects button | Query | Crosstab Query Wizard

Dialog: Crosstab Wizard

Icon: Mouse click View each step of the wizard:
1  2  3  4  5 

Step 1: Pick source for data: Table: Day Camp Campers & Staff
The bottom portion of the dialog will change as you make choices.

Step 2: Pick fields to use a row headings. You can use more than one.

Step 3: Pick field to use for column headings. Only 1.

Step 4: Pick value to show in cells. There is a checkbox for including a sum of each row.

Step 5: Pick a name for the query.

Design View of the query:

Query Design View: CrosstabThe 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).

Icon: Step-by-Step 

Step-by-Step: Crosstab Query

 Icon: Step-by-Step

What you will learn:

to use the Crosstab Query Wizard

Start with:  Class disk, Projects database open.

The tables in the Projects database are clearly not complete. You will, however, construct a crosstab query that will help when there are many more records in the tables. Your crosstab query will count the number of people with a particular Title for each Country/Region.

  1. Switch, if necessary, to the Database Window.

  2. Dialog: New Query - Crosstab Query WizardButton: New Object - QueryClick on the New Objects button and select New Query.
    The New Query dialog opens.

  3. Select Crosstab Query Wizard and click on OK.
    The wizard opens.

  4. Crosstab Query Wizard: step 1 - choose data sourceStep 1 - Source:
    Select the table Staff.
    Click Next.

  5. Crosstab Query Wizard: step 2 - choose row heading(s)Step 2 - Row heading(s):
    Select Country/Region and click the > button to move the field to the Selected Fields list.

    Click Next.

  6. Crosstab Query Wizard: step 3 - choose column headingStep 3 - Column heading:
    Select Title.
    Click Next.

  7. Crosstab Query Wizard: step 4 - choose valueStep 4 - Value:
    Select Count.
    Click Next.

  8. Crosstab Wizard - step 5- name the queryStep 5 - name:
    Click on Modify the design.
    Click Finish.
    The query is automatically saved.
    Query Design view:Crosstab queryThe wizard closes and the query opens in Query Design view.

  9. Icon: Run Run the query.
    Query Datasheet View: Crosstab Query
    The column Total of EmployeeID is a sum for the row, the number of employees in each country. The other columns count the number of employees with that title in that country.
    TipExchanging the row and column choices in the wizard would give you the number of employees for each job title.

  10. Close the query. (It is already saved.)