Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Jan's CompLit 101 > Working with Databases > Tables & Queries > Queries > Crosstab Query
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

Tables & Queries: Queries: Crosstab Query

Button: Crosstab queryA 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 some other calculation.

Example: Averages

A crosstab query can create a datasheet of score averages, like the one below from the Language Arts Classes Icon: On Site database. Student names and ID numbers are listed down the left (2 sets of row headings) and the subject/type categories are across the top (column headings), like Reading Daily, Reading Quiz, Reading Test, etc. The value in the cell is the average of the scores that the student (row) made on assignments in that subject/type (column).

Example: Crosstab query

Molly McCoy's average for English Daily work is 68.

A similar crosstab query might calculate the term grade for a subject.


Example: Crosstab Query - Count with row totals

This example comes from the Day Camp Icon: On Site database in your resource files.

T-shirt Problem: On the first day of a summer day camp how many T-shirts of what sizes do you need?
Each unit has a different number of campers and staff.

In this example, (from a database created for a real day camp) there are 12 units including 'All' for staff not assigned to a unit, 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. That could be from a data entry error or else a registration form that did not indicate 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. 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 by default. To sort columns in order by increasing or decreasing size (X, L, M, S), we would need another table that ordered these sizes and had a relationship to the T-shirts table.

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

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) T-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 Crosstab Query 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.

You start the Crosstab Query Wizard from the Query Wizard button Button: Query Wizard (Access 2010):
  Create ribbon tab > Query Wizard > Crosstab Query Wizard

Crosstab Query Wizard: Pick a source

Step 1 > Step 2 > Step 3 > Step 4 > Step 5

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 the Crosstab 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 from previous lesson 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. This shows you how many agents, how many branch managers, etc. are in the country or region.

  1. On the Create ribbon tab, click on the Query Wizard button Button: Query Wizard (Access 2010).
    The New Query dialog opens.
     

     

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

  3. Crosstab Query Wizard: step 1 - choose data sourceStep 1 - Source:
    Select the table Staff and click
    Next >
    .
     

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

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

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

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

    Notice that there is a column for EmployeeID and one for the Total Of EmployeeID:[EmployeeID]
     

  8. Query Datasheet View: Crosstab Query

    Icon: Run Run the query.

    Icon: Datasheet View 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 job title in that country.
     
    TipExchanging the row and column choices in the wizard would give you the number of employees for each job title.

  9. Button: Save (Access 2010) Close the query and save the changes.